Code to condition format the cell colors and font colors of a range values

[mrprofit]

how to use VBA code to condition format a range of cells D:D by reference to the color and font color of cell in range B:B,

thanks in advance,


condition format cell color.xlsm

[snb]

Here you go:

Sub M_snb()
    sn = [B1:B20]
    sp = [D1:D20]
    
    For j = 1 To UBound(sp)
       Cells(j, 6).Interior.Color = Cells(Application.Match(sp(j, 1), sn, -1) + Abs(IsError(Application.Match(sp(j, 1), sn, 0))), 2).Interior.Color
    Next
End Sub

[mrprofit]

OK, it did work, thanks for the help, appreciate it,

one more thing, how to change the font color to match B ?

If i want to apply it to cells (F1:F30, H10:H40), instead of D1:D20, what to change the code, ?

[mrprofit]

it doen't seems to condition format the range, is it just copy the colot to Column F?

[snb]

Conditional formatting is meant for those who have no knowledge of VBA.

[mrprofit]

i find that there is alternate mthod using VBA to code the excel condition format, how to use the code to apply to my condition format formula?