Conditional relative formatting??? Excel 2007

[Tulsi17]

Help I have three columns and 5000 rows. I was trying to use icon sets to compare the three cells in each row and return an appropriate icon: red is for greatest value; yellow for medium value; green for least value. These will not always be in the same order nor are they related to a specific value.
Then! I wanted to copy/paste the formula for each of the five thousand rows (of course, dragging or paste special formats? would be great, if it works). I can't get the syntax right or, frankly, I just don't know how to ask the right question of Excel. Can anyone help? Thanks.

 

[mharden]

Have you divided the values in the cells into the 3 groups for the icons? For example if all of the values on the sheet range between 0 - 100, you might want 1-33 for green, 34-66 for yellow, and 67-100 for red.Green would be when the value <=33; yellow would be when the value is <=66, and red would be <=100. Also, make sure you choose the correct type.Once you do that then select the cells you want to apply the formatting to, go to edit rule and manipulate the values choices until you get the desired effect. Hope this helps.

 

[ZAX]

red is for greatest value; yellow for medium value; green for least value. These will not always be in the same order nor are they related to a specific value.

Can you specify Like "red For Values Greater Than X and green for values less than Y" ?

 

[Tulsi17]

Can you specify Like "red For Values Greater Than X and green for values less than Y" ?

Hi and thanks for your response. x and y are not constant. The outcome (colors) would be determined by their relationship to each other. Row one may be A1 = 150, B1 = 200, C1 = 300.
Row 2 may be A2 = 3500; B2 = 4300, C2 = 1.
In Row one, A1 would be green; B1 would be yellow; C1 would be red.
In Row 2, A2 would be yellow because it is valued between B2 and C1; B2 would be red because it is valued greater than both A2 and C2, C2 would be green because it is valued lowest of the three.
No idea how to formulate this. ugh.

 

[Tulsi17]

Can you specify Like "red For Values Greater Than X and green for values less than Y" ?

Hi and thanks for your response. x and y are not constant. The outcome (colors) would be determined by their relationship to each other. Row one may be A1 = 150, B1 = 200, C1 = 300.
Row 2 may be A2 = 3500; B2 = 4300, C2 = 1.
In Row one, A1 would be green; B1 would be yellow; C1 would be red.
In Row 2, A2 would be yellow because it is valued between B2 and C1; B2 would be red because it is valued greater than both A2 and C2, C2 would be green because it is valued lowest of the three.
No idea how to formulate this. ugh.

 

[ZAX]

Here's your peace of code and it works as expected.

Sub ColorCells()
For i = 1 To 5000
Cells(i, Application.Match(Application.Max(Range(Cells(i, 1), Cells(i, 3))), Range(Cells(i, 1), Cells(i, 3)), 0)).Interior.Color = vbRed
Cells(i, Application.Match(Application.Min(Range(Cells(i, 1), Cells(i, 3))), Range(Cells(i, 1), Cells(i, 3)), 0)).Interior.Color = vbGreen
Do
s = Application.RandBetween(1, 3)
Loop While s = Application.Match(Application.Max(Range(Cells(i, 1), Cells(i, 3))), Range(Cells(i, 1), Cells(i, 3)), 0) Or s = Application.Match(Application.Min(Range(Cells(i, 1), Cells(i, 3))), Range(Cells(i, 1), Cells(i, 3)), 0)
Cells(i, s).Interior.Color = vbYellow
Next
End Sub

ZAX

 

[Tulsi17]

ZAX, thank you! I really appreciate your help.