conditional formatting by comparing dynamic values : Excel

Question

My code :

=IF(AND(B2="Lodavan-Boleropickup",C2<"30"),CELL("color",D2),"")

It didn't work.

Please refer the below table. I need to highlight the cells under "Mileage" column.

Example:

"Lodavan-Boleropickup" (target mileage is 30) I need to highlight the cells in "Mileage" column which are below 44. "Suzuki-Fabric" (target mileage is 50) I need to highlight the cells in "Mileage" column which are below 55.

The condition is it has to refer the column "Vehicle Name" and then check the value "Mileage", then it has to compare with the target value and then highlight. Any possible easy way is fine.

Either compare the pre-defined table like:

My requirement: If the variable from "Vehicle Name" column is "Loadvan-Boleropicup" Then it has to compare the value from "Mileage column" with the "Target" which we need to define and then it has to highlight the cells.

Answer 1

may be these two solutions help you:

solution 1) insert a column named selected_names in first table and write this formula in first cell init and fill-down to copy. =SetColor(IF(INDEX($C$2:$C$12;MATCH(F2;$B$2:$B$12;0))>G2;F2;"");IF(INDEX($C$2:$C$12;MATCH(F2;$B$2:$B$12;0))>G2;F2;""))

and you need write this function in VBA editor:

    Option Explicit

    Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
    Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr) As Long
    Declare PtrSafe Function GlobalAddAtom Lib "kernel32" Alias "GlobalAddAtomA" (ByVal lpString As String) As Integer
    Declare PtrSafe Function GlobalGetAtomName Lib "kernel32" Alias "GlobalGetAtomNameA" (ByVal nAtom As Integer, ByVal lpBuffer As String, ByVal nSize As Long) As Long

Public Function SetColor(ByVal Value As Variant, ByVal BackGroundColor As String) As Variant
    SetTimer Application.hwnd, GlobalAddAtom(Application.Caller.Address(External:=True) & "*" & BackGroundColor), 0, AddressOf ChangeColor
    SetColor = Value
End Function

Sub ChangeColor(ByVal hwnd As Long, ByVal uMsg As Long, ByVal nIDEvent As Long, ByVal dwTimer As Long)
    Dim sBuffer As String, lRet As Long, lColorIndex As Long

    On Error Resume Next
    KillTimer hwnd, nIDEvent
    sBuffer = Space(256)
    lRet = GlobalGetAtomName(nIDEvent, sBuffer, Len(sBuffer))
    sBuffer = Left(sBuffer, lRet)
    Select Case LCase(Split(sBuffer, "*")(1))
        Case "name1"
            lColorIndex = 17
        Case "name2"
            lColorIndex = 18
        Case "name3"
            lColorIndex = 19
        Case "name4"
            lColorIndex = 20
        Case "name5"
            lColorIndex = 21
        Case "name6"
            lColorIndex = 22
        Case "name7"
            lColorIndex = 23
        Case "name8"
            lColorIndex = 24
        Case "name9"
            lColorIndex = 25
        Case "name10"
            lColorIndex = 26
        Case "name11"
            lColorIndex = 27
        Case ""
            lColorIndex = xlColorIndexNone
    End Select
    Range(Split(sBuffer, "*")(0)).Interior.colorIndex = lColorIndex
End Sub

refrence link for setting cell color :https://www.mrexcel.com/board/threads/change-cell-color-inside-formula.1050178/

solution 2) you can insert column selected_names and use this formula in first cell and fill-down to copy to all cells. =IF(INDEX($C$2:$C$12;MATCH(F2;$B$2:$B$12;0))>G2;F2;"") all conditional rows will be marked, then you can filter them by similar name, and no need to VBA function for coloring match names (this way is better in my idea).