CODEWITHSUNDEEP
vbaexcel
geocc
geocc

Reputation: 31

VBA colour highlight based on IF

So I am trying to run the code below. It should be straight to the point, and I don't understand why it's not working. If cell b2 is "John", and cell O2 is empty, then O2 should be red. If O2 is not empty, then it should not be colored.

Any help is must appreciated.

Sub columnO(d As Long)

If Cells(d, "B") = "John" And Cells(d, "O") = "" Then    
    Cells(d, "O").Interior.Color = RGB(255, 0, 0)
Else
    Cells(d, "O").Interior.Color = RGB(1000, 1000, 1000)
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Range("B10:O10000"), Target) Is Nothing Then
    columnO Target.Row
End If

End Sub

Upvotes: 0

Views: 31

Answers (2)

Flaniganga
Flaniganga

Reputation: 67

Why don't you rather use conditional formatting instead of VBA? Might be easier to manage

Upvotes: 0

MarcinSzaleniec
MarcinSzaleniec

Reputation: 2256

This can be easily done with conditional formatting. Just pick conditional formatting from Home menu on your ribbon --> new rule -->Use a formula to determine... and type formula

=$B$2<>"John"

Then you need only set your desired format. You may toggle with $ in formula to allow dragging and copying the format if you need it.

Upvotes: 2

Related Questions