I have been struggling with this for a while now. What I have works but is slow. I have a pivot table that I am generating in VBA on command and then I use the code below to run a conditional format. my problem is that I need row to have each number only compared to the others in the same row not the whole table. So at this point I am looping through the whole table. The problem is that the table can be a few thousand rows long and it taking forever and excel stops responding from time to time. And I'm doing this on 2 different sheets to boot.
This is my loop. It has to be able to grow with the table length and width wise as one table is a set number of columns but the other table's number of columns increase hourly.
I'm writing this in xl2010 but need it to work in xl2007. Any help would be appreciated. I've been self teaching myself this via google for the most part.
This is my loop. It has to be able to grow with the table length and width wise as one table is a set number of columns but the other table's number of columns increase hourly.
Code:
Do Until Range("A" & r).Value = "Grand Total"
With Range(Cells(r, 2), Cells(r, i))
.FormatConditions.AddColorScale ColorScaleType:=3
With .FormatConditions(1)
With .ColorScaleCriteria(1)
.Type = xlConditionValueLowestValue
With .FormatColor
.Color = 8109667
.TintAndShade = 0
End With
End With
With .ColorScaleCriteria(2)
.Type = xlConditionValuePercentile
.Value = 50
With .FormatColor
.Color = 8711167
.TintAndShade = 0
End With
End With
With .ColorScaleCriteria(3)
.Type = xlConditionValueHighestValue
With .FormatColor
.Color = 7039480
.TintAndShade = 0
End With
End With
End With
End With
r = r + 1
Loop
I'm writing this in xl2010 but need it to work in xl2007. Any help would be appreciated. I've been self teaching myself this via google for the most part.