I have a sheet containing (among others) 3 columns of data - I have applied Conditional Formatting to Each column in code like this;
That is just 1 Colour, I have three similar blocks that colour the bottom three YELLOW, the bottom 2 BLUE and the bottom 1 GREEN (above) giving me a Lowest = Green, Next Lowest = Blue and 3rd Lowest = Yellow (only one square of each in wach Column), that works.
Data is being fed into the sheet and the positions change often - I want to trap a condition where all the three affected cells in a single row are the same colour, like all Green etc.
I tried testing the Interior.Color using an If stattement typed into an adjacent cell but discovered that you cant do that.
I am to sure how to go about this and am hoping someone has a few ideas that might lead me to a solution. I imagine that I will never have more than 30 - 40 rows of numbers to deal with (if that makes a difference) - it's just the method of knowing when one row contains all the lowest (or highest) values in one calculation cycle. The rows are all updated about 3 times a second (maybe a little faster in the future).
Any help or pointers appreciated.
Thanks
Code:
Sub Add_CF_1(pRange As Range) ' Top 1 Green ' Blue 16315608
pRange.Select
Selection.FormatConditions.AddTop10
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.TopBottom = xlTop10Bottom
.Rank = 1
.Percent = False
End With
With Selection.FormatConditions(1).Font
.Color = Sheet3.Range("C8")
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = Sheet3.Range("D8")
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
That is just 1 Colour, I have three similar blocks that colour the bottom three YELLOW, the bottom 2 BLUE and the bottom 1 GREEN (above) giving me a Lowest = Green, Next Lowest = Blue and 3rd Lowest = Yellow (only one square of each in wach Column), that works.
Data is being fed into the sheet and the positions change often - I want to trap a condition where all the three affected cells in a single row are the same colour, like all Green etc.
I tried testing the Interior.Color using an If stattement typed into an adjacent cell but discovered that you cant do that.
I am to sure how to go about this and am hoping someone has a few ideas that might lead me to a solution. I imagine that I will never have more than 30 - 40 rows of numbers to deal with (if that makes a difference) - it's just the method of knowing when one row contains all the lowest (or highest) values in one calculation cycle. The rows are all updated about 3 times a second (maybe a little faster in the future).
Any help or pointers appreciated.
Thanks