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

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,


Attachment 1811

Here you go:

Code:

---

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

---

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, ?

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

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

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?