Excel Conditional Formatting VBA Loop

Question

I am trying to save myself some time by using VBA to highlight the row that has a value that matches a cell. I can get it to work on a single row but would like it to work on a loop to capture ~97 rows.

The Value to match is in Cell A4 in column U7:U97 and therefore highlight the Range("E7:K7,M7:S7,U7:V7")

The code I have is:

Sub Macro1()

    Range("E7:K7,M7:S7,U7:V7").Select
    Range("U7").Activate
    Application.CutCopyMode = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$U$7=$A$4"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0.249946592608417
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

This is working correctly for row 7 and I can cut and paste this and manually change the values for the next row, but there must be a way to run this in a loop.

My Attempt is below that is not working,

Sub Macro2()

    For i = 7 To 97

    Sheet2.Cells(i, "E").Select
    Sheet2.Cells(i, "F").Select
    Sheet2.Cells(i, "G").Select
    Sheet2.Cells(i, "H").Select
    Sheet2.Cells(i, "I").Select
    Sheet2.Cells(i, "J").Select
    Sheet2.Cells(i, "K").Select
    Sheet2.Cells(i, "L").Select
    Sheet2.Cells(i, "U").Activate
    Application.CutCopyMode = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=Sheet2.Cells(i, 21)=$A$4"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0.249946592608417
    End With
    Selection.FormatConditions(1).StopIfTrue = False

    Next i

End Sub

Why are you using VBA, and not Conditional Formatting without it? Also, what's not working with the code? Are errors thrown? What error, where? Also, you can help to tighten up the code by [avoiding the use of `.Select/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). (For instance, in `Macro2`, the lines selecting columns E through K don't do anything, as you select column L at last, and that becomes `Selection`). — BruceWayne, May 07 '17 at 06:16

score 1 · Accepted Answer · answered May 07 '17 at 06:54

Lazy quick fix (untested):

Sub Macro1()

    Dim i As Long

    For i = 7 to 97


    Range(Replace("E#:K#,M#:S#,U#:V#","#",i)).Select
    Range("U" & i).Activate
    Application.CutCopyMode = False
    Selection.FormatConditions.Add Type:=xlExpression, _
                                   Formula1:="=$U$" & i & "=$A$4"      

  Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0.249946592608417
    End With
    Selection.FormatConditions(1).StopIfTrue = False

    Next i

End Sub

Excel Conditional Formatting VBA Loop

1 Answers1