# Conditional relative formatting??? Excel 2007

#### Tulsi17

##### New Member
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.

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### mharden

##### New Member
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

##### Well-known Member
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

##### New Member
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

##### New Member
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

##### Well-known Member
Here's your peace of code and it works as expected.
Code:
``````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

##### New Member
ZAX, thank you! I really appreciate your help.

Replies
1
Views
380
Replies
1
Views
103
Replies
0
Views
129
Replies
1
Views
1K
Replies
3
Views
714

1,195,849
Messages
6,011,957
Members
441,657
Latest member
Diupsy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back