Highlight a row when they are equal on the adjacent cell VBA

[rickjason]

I have column A and Column B, how to highlight the cell when they are equal?

Sample and Expected Result

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Apple[/TD]
[TD]Banana[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Banana[/TD]
[TD]Apple[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Apple[/TD]
[TD]Apple[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Banana[/TD]
[TD]Apple[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I tried to do this by doing Conditional Formatting, it works, however my rows are dynamic and blank cells bottom down after the last row with value are also captured.

Thanks.

 

[Trebor76]

Hi rickjason,

Try this macro:

Option Explicit
Sub Macro5()

    Dim lngLastRow As Long
    Dim lngMyRow   As Long
    
    Application.ScreenUpdating = False
    
    lngLastRow = Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
    
    For lngMyRow = 2 To lngLastRow
        If StrConv(Range("A" & lngMyRow), vbProperCase) = StrConv(Range("B" & lngMyRow), vbProperCase) Then
            Range("A" & lngMyRow & ":B" & lngMyRow).Font.Color = RGB(255, 0, 0)
        Else
            Range("A" & lngMyRow & ":B" & lngMyRow).Font.Color = RGB(0, 0, 0)
        End If
    Next lngMyRow
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[rickjason]

Hi Trebor,

Tried it, and it's perfect

Thank you so much.

 

[Trebor76]

Thanks for letting us know and you're welcome