cell coloring based on value without loop or conditional format

[gordsky]

Im using the below to change a range of cells if the cell value = BH and do nothing if it doesnt.

What I would like to do is to colour the cell if it = BH rather than change the value. I know I can do it with a loop or conditional formatting but is there a way to adapt the code below to do it in one hit.

[d2:j53] = [if(d2:j53="BH","Bank",d2:j53)]

any help much appreciated

 

[mumps]

Try:

Sub ColorCell()
    With Application.ReplaceFormat
        .Clear
        .Interior.ColorIndex = 6
        Range("D2:J53").Replace "BH", "BH", xlWhole, xlByRows, False, , False, True
        .Clear
      End With
End Sub