CODEWITHSUNDEEP
vbaexcel
Nemo51
Nemo51

Reputation: 15

Excel Conditional Formatting VBA Loop

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

Upvotes: 1

Views: 2194

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

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

Upvotes: 1

Related Questions