title: Excel Conditional Formatting VBA Loop
body: <p>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.</p>
  
  <p>The Value to match is in Cell A4 in column U7:U97 and therefore highlight the Range("E7:K7,M7:S7,U7:V7") </p>
  
  <p>The code I have is:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>My Attempt is below that is not working,</p>
  
  <pre><code>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
  </code></pre>