Simple conditional formatting question

[Microsoft]

Hi guys,

I would like to highlight every row/column where a specific word is found. However, I want the highlighting to stop at the end of the data.

E.g. if A1:A20 includes "Yes" or "No" I use =$A1="Yes". I want the formatting to stop at A20. How is this possible please?

Thanks in advance.

 

[MrJoosten]

Hello Microsoft,

I played with the recorder and came up with this:

Try

Sub format()
 
    Dim lr As Long
    
    lr = Cells(Rows.Count, 1).End(xlUp).Row 'counts rows used in column A 
    
    Range("A1:A" & lr).Select 'select range A1 to last used cel in column A 
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""d"""  'put here your specific word
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    
    With Selection.FormatConditions(1).Interior 
        .PatternColorIndex = xlAutomatic
        .Color = 255 'color is red
        .TintAndShade = 0
    End With
   
End Sub

Hope this helps you

 

[Special-K99]

This doesn't make sense.
if A1:A20 includes "Yes" or "No" I use =$A1="Yes".

What happened to the check for "No" ?

Your formula should be =OR(A1="Yes","A1="No")
Use the format painter to copy down the column.

If A21 doesnt contain Yes ro No then the formatting wont show at A21.