editing code to change colour scales.

[mattianperry]

hello

I need some help editing a piece of code. i would like to know how to edit some code so that the colour scales used in the code are changed. At the moment in the code given on the below link, it highlights high numbers in red and low numbers in green. I would like to know how to reverse this please so high numbrs are in green.

thanks


http://www.excelforum.com/excel-gene...le-sheets.html

[Andy Pope]

Your link does not work.

If all you want to do is switch the colours can you not just edit the colour values.

[mattianperry]

Hi ,

I tried just changing the colour scale type from 3 to 1 to see what that did but then when i tried to run the code it would not work. apologies but im very new to vba.

thanks

Sub Macro1()
'
' Macro1 Macro
'
Dim rngData As Range
Dim rngRow As Range
Dim xx As Worksheet

ActiveSheet.Cells.FormatConditions.Delete

Set rngData = Range("B2:X219")
For Each rngRow In rngData.Rows
With rngRow
.FormatConditions.AddColorScale ColorScaleType:=3
.FormatConditions(.FormatConditions.Count).SetFirstPriority
.FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValueLowestValue
With .FormatConditions(1).ColorScaleCriteria(1).FormatColor
.Color = 8109667
.TintAndShade = 0
End With
.FormatConditions(1).ColorScaleCriteria(2).Type = xlConditionValuePercentile
.FormatConditions(1).ColorScaleCriteria(2).Value = 50
With .FormatConditions(1).ColorScaleCriteria(2).FormatColor
.Color = 8711167
.TintAndShade = 0
End With
.FormatConditions(1).ColorScaleCriteria(3).Type = xlConditionValueHighestValue
With .FormatConditions(1).ColorScaleCriteria(3).FormatColor
.Color = 7039480
.TintAndShade = 0
End With
End With
Next

End Sub

[Andy Pope]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Please Login or Register  to view this content.

[mattianperry]

thanks very much that works great now.

sorry about the code i sent.