I'm trying to make a code that will format each row individually using the first option in the color scale formatting on the selected cells. it seems to recognize the change in the cells and is pointing to the right ones as far as I can tell but it will only format the first row of the selection. does anybody know why this is happening? I'm not familiar with VBA formatting. Any help would be greatly appreciated!!
Sub ColorScaleFormatPerRow()
Dim a As Range, b As Range, i As Integer
Set a = Selection
' Range(a.Cells(1, 1), a.Cells(1, a.Columns.Count)).Select
For i = 1 To a.Rows.Count
Range(a.Cells(i, 1), a.Cells(i, a.Columns.Count)).Select
Range("AA2").Activate
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 = 7039480
.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 = 8109667
.TintAndShade = 0
End With
Next i
End Sub