0

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

0 Answers0