CODEWITHSUNDEEP
excelvba
P.B
P.B

Reputation: 1

Conditional formating VBA

I have a range of the data that i would like to use a conditional formationg for each row of my data, while the highest value has red color and lowest value has gree color. please see the example picture:

enter image description here

for that I have used the following code:

For k = 33 To  65
 
    With WBSim.Sheets("Country_1").Rows(k)

        .FormatConditions.AddColorScale ColorScaleType:=3
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        .FormatConditions(1).ColorScaleCriteria(1).Type = _
                  xlConditionValueLowestValue
                    
    End With

But in the result the highest value has green color and the lowest value has Red color. Could you please let me know Hoe can i fix this problem?

Bests, Parisa

Upvotes: 0

Views: 41

Answers (1)

norie
norie

Reputation: 9857

That the default setting when applying color scales, you need to add code to change the colors for the lowest/highest values.

This code will do it but you might want to play about with the colors to get exactly what you want.

Dim K As Long

    For K = 33 To 65

        With WBSim.Sheets("Country_1").Rows(K)
            .FormatConditions.AddColorScale ColorScaleType:=3
            With .FormatConditions(1)
                .SetFirstPriority
                With .ColorScaleCriteria(1)
                    .Type = xlConditionValueLowestValue
                    .FormatColor.Color = 5287936
                End With
                With .ColorScaleCriteria(2)
                    .Type = xlConditionValuePercentile
                    .FormatColor.Color = 8711167
                End With
                With .ColorScaleCriteria(3)
                    .Type = xlConditionValueHighestValue
                    .FormatColor.Color = RGB(255, 0, 0)
                End With
            End With
        End With
        
    Next K

Upvotes: 1

Related Questions