title: Is there a way to create a conditional formatting loop in VBA?
body: <p>I have a large table spanning from D5 to AM39. Each column has its average value in cell D40, E40, F40, etc. I want to format the cells so that if the number in that column is higher than the average, color green and if lower color red.</p>
  <p>I am extremely new to VBA but have this script thus far that is supposed to color cells greater than average but does not work (I think it has something to do with Cells(4,39) index being wrong, but am not sure.</p>
  <pre><code>Application.CutCopyMode = False
  
  With Range(Cells(5, 39), Cells(4, 39))
    .FormatConditions.Delete
  
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
      Formula1:=&quot;=$D40&quot;
   .FormatConditions(1).Interior.color = RGB(0, 150, 0)
  
  End With
  End Sub
  </code></pre>
  <p>Appreciate any tips</p>
  <p>EDIT********</p>
  <p>Using the record macro feature I believe I have a closer solution to what I am looking for, however, the formatting doesn't align with the averages per row (cells are red that should be green, and vice versa)</p>
  <pre><code>With Range(Cells(39, 4), Cells(5, 39)).Select
  Application.CutCopyMode = False
  Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
      Formula1:=&quot;=D$40&quot;
  Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  With Selection.FormatConditions(1).Font
      .color = -16752384
      .TintAndShade = 0
  End With
  With Selection.FormatConditions(1).Interior
      .PatternColorIndex = xlAutomatic
      .color = 13561798
      .TintAndShade = 0
  End With
  Selection.FormatConditions(1).StopIfTrue = False
  Application.CutCopyMode = False
  Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
      Formula1:=&quot;=D$40&quot;
  Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  With Selection.FormatConditions(1).Font
      .color = -16383844
      .TintAndShade = 0
  End With
  With Selection.FormatConditions(1).Interior
      .PatternColorIndex = xlAutomatic
      .color = 13551615
      .TintAndShade = 0
  End With
  Selection.FormatConditions(1).StopIfTrue = False
    End With
    End Sub
  </code></pre>