title: Conditional formatting in Excel Macro only working on first cell of selection
body: <p>I'm trying to automate the creation of some pivot tables for a report that I need. I recorded a Macro to make the necessary changes, and it mostly functions correctly, apart from missing the conditional formatting. It works on the top cell of the selection, but that's all. </p>
  
  <p>I'm aware that there has been issues with Macros in Excel and Conditional formatting, but I didn't find any answers to solve this problem. The section of the code that I believe applies to that condition is below.</p>
  
  <pre><code>    Range("G6:G13").Select
  Selection.FormatConditions.AddColorScale ColorScaleType:=3
  Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
      xlConditionValueLowestValue
  With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
      .Color = 8109667
      .TintAndShade = 0
  End With
  Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
      xlConditionValuePercentile
  Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
  With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
      .Color = 8711167
      .TintAndShade = 0
  End With
  Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
      xlConditionValueHighestValue
  With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
      .Color = 7039480
      .TintAndShade = 0
  End With
  Selection.FormatConditions(1).ScopeType = xlSelectionScope
  Range("I6:I13").Select
  Selection.FormatConditions.AddColorScale ColorScaleType:=3
  Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
      xlConditionValueLowestValue
  With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
      .Color = 8109667
      .TintAndShade = 0
  End With
  Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
      xlConditionValuePercentile
  Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
  With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
      .Color = 8711167
      .TintAndShade = 0
  End With
  Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
      xlConditionValueHighestValue
  With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
      .Color = 7039480
      .TintAndShade = 0
  End With
  Selection.FormatConditions(1).ScopeType = xlSelectionScope
  </code></pre>
  
  <p>Current result I'm seeing:
  <a href="https://i.stack.imgur.com/KmZs8.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/KmZs8.png" alt="Current result I&#39;m seeing"></a></p>
  
  <p>and the desired outcome</p>
  
  <p><a href="https://i.stack.imgur.com/bSIMS.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bSIMS.png" alt="The Desired outcome"></a></p>
  
  <p>Thanks in advance for any help!</p>