I am trying to compare two columns on the same sheet.

[pveke]

Such that if column A has the entry Escalation Standby and column B has the entry Absent then column B should be formatted with a red colour otherwise green. I kept getting different errors one being compile error. Kindly assist



Sub conditional_formatting()
Dim aRng, bRng As Range
Set aRng = Range("AC5:A175")
Set bRng = Range("AD5:AD175")

For Each acell In aRng
For Each bcell In bRng
If acell Like "Escalation Standby" And bcell Like "Absent" Then
bcell.Interior.ColourIndex = 3
Else: bcell.Interior.ColourIndex = 10
End If
Next
Next
For Each ccell In aRng
For Each dcell In bRng
If ccell = "Standby" And dcell = "Absent" Then
dcell.Interior.ColourIndex = 3
Else: dcell.Interior.ColourIndex = 10
End If
Next
Next
For Each ecell In aRng
For Each fcell In bRng
If ecell Like "Attend" And fcell Like "Attend" Then
fcell.Interior.ColourIndex = 10
Else: fcell.Interior.ColourIndex = 3
End If
Next
Next
For Each gcell In aRng
For Each hcell In bRng
If gcell Like "N/A" Then
hcell.Interior.ColourIndex = 10
End If
Next
Next
For Each icell In aRng
For Each jcell In bRng
If jcell Like "Absent" Then
jcell.Interior.ColourIndex = 3
End If

Next
Next

End Sub

 

[Fluff]

Duplicate how do i solve the for without next error i keep getting

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).