Excel VBA Macro Conditional Formatting with Intersect

Question

Hi Everyone,

I wrote an Excel VBA macro to do conditional formatting using an intersect of two columns but I can't get it to work for some reason. If anyone has any idea on what I can do to fix it, I would sincerely appreciate it.

I want to highlight both the source and the target columns in which there is a match or duplicate as follows:

E Column (Target) 0.0000% 0.0000% 11.1803% 12.7775% 13.7190% 13.9841% 13.9841% 14.5698% 14.9071% 15.5746% 15.6492% 16.1355% 16.1355% 16.3123% 16.3123% 19.0693% 19.4511% 21.9089% 21.9089% 21.9089%

V Column (Source) 13.7190% 14.9240% 15.4919% 20.4521% 21.5725% 23.3319% 23.7718% 24.1871% 25.7257% 27.2166% 28.2290% 29.7543% 29.7543% 30.4968% 31.0080% 31.9022% 32.8570% 33.3333% 33.3333% 34.7434% 34.9603% 34.9927% 36.4516% 36.8697% 37.5637% 38.2046% 38.6151% 38.7298% 38.7298% 39.3830% 40.2694% 41.8330% 42.2049%

Sub Highlight_rsd_5batch()
Dim WatchRange As Range, Target As Range, cell As Range
Set Target = Range("E19:E237") 'change column ref as required
Set WatchRange = Range("V19:V237")

For Each cell In Target.Cells
If Intersect(Target, WatchRange) Is Nothing Then
cell.Interior.ColorIndex = xlNone
Else: cell.EntireRow.Interior.ColorIndex = 6
End If
Next cell
End Sub

Answer 1

The Intersect function checks to see if the two ranges have any cells in common, not if they have values in common. You could use the CountIf function instead:

Sub Highlight_rsd_5batch()
    Dim WatchRange As Range, Target As Range, cell As Range
    Set Target = Range("E19:E237") 'change column ref as required
    Set WatchRange = Range("V19:V237")

    For Each cell In Target.Cells
        If Application.WorksheetFunction.CountIf(WatchRange,cell.Value) > 0 Then
            cell.Interior.ColorIndex = 6
            For Each watchCell in WatchRange.Cells
                If watchCell.value = cell.Value Then: watchCell.Interior.ColorIndex = 6
            Next watchCell
        Else: cell.EntireRow.Interior.ColorIndex = xlNone
        End If
    Next cell
End Sub

This task does not really require the use of VBA and could be accomplished using the same formulas in the Conditional Formatting tools under Format>Conditional Formatting. See the linked tutorial for more help.