I have 4 columns of prices from 4 suppliers -

Microsoft Office excel 2007 - upgrade
August 25, 2010 at 01:49:29
Specs: Windows 7
I have 4 columns of prices from 4 suppliers - can conditional formatting colour these red to green across the columns rather than across the range of prices? For example item 1 row 1 compare the four prices received that month?

All i can get it to do is colour in that way but across all four columns and 50 rows...

Many thanks in advance !

See More: I have 4 columns of prices from 4 suppliers -

Report •

August 25, 2010 at 07:26:53
I have Excel 2003, so I can only test this with 3 conditions. I believe with 2007, you should be able to set up at least 4 Conditional Formatting rules.

Here's what I did for 3 conditions in Excel 2003 - Modify this as required for 2007.

Using A1:C1 as an example, select the range.

Under Format...Conditional Formatting, set up 3 conditions using "Formula Is..." and choose a color for each:

Condition 1:

=CHOOSE(RANK(A1,$A$1:$C$1),1,0,0) Red

Condition 2:

=CHOOSE(RANK(A1,$A$1:$C$1),0,1,0) Yellow

Condition 3:

=CHOOSE(RANK(A1,$A$1:$C$1),0,0,1) Green

Note the position of the "1" in each formula.

This will RANK the 3 values in A1:C1 and then use CHOOSE to find the "1". Based on which cell returns the "1", which CF will interpret as TRUE, CF will color the highest value Red , the middle value Yellow and the lowest value Green .

Equal values will have the same color based on where they rank.

Report •

August 31, 2010 at 04:45:14
Thanks for prompt response - however i am struggling to get that to do anything in my version of excel .....

Report •

August 31, 2010 at 05:47:19
re: "however i am struggling to get that to do anything in my version of excel"

Is it because you don't know how to set up Conditional Formatting in Excel 2007 or is there some other issue?

Here is just one of many sites that explain how to use CF in 2007. Google around for more.


Report •

Related Solutions

August 31, 2010 at 05:58:04
From the Ribbon select Home - Styles - Conditional Formatting,
From the drop down select 'Manage Rules' and select 'New Rule'
In the dialog box select the last item in the list 'Use a formula to determine which cells to format'
Enter the first formula in the box
Click the format button and from the 'Fill' Tab select the first color, click OK
Click OK and select 'New Rule'
Select 'Use a formula to determine which cells to format'
Enter the second formula in the box.
Click the format button and from the 'Fill' Tab select the second color, click OK
Repeat for third and fourth conditional format formulas.
When all four have been entered, 'Applies to' will show the selected range that the conditional formatting will apply to, and the 'Stop if True' boxes should be checked.
Click Apply and OK


Report •

August 31, 2010 at 06:23:24
No I play with CF quite a bit, I will try Humar's steps next and post back.

Report •

August 31, 2010 at 06:30:47
Note: For 4 conditions, I believe you'll have to extend the formula to check for 4 ranks, not 3.

In other words, note the extra 0.


Perhaps Humar can test the 4 conditions and see if my suggestions work.

(I've got Excel 2010 at home...I guess I should install it someday.)

Report •

August 31, 2010 at 06:42:58
!! Very many thanks to you both - Humar - set that up in a blank sheet using 1,2,3 and got it to work - just need to adjust the formula to work in my sheet but i'll try that now - many thanks again!!

Report •

August 31, 2010 at 06:44:45
Understood DerbyDad - thought that may be the case - anyone know how many it can process, 6,7,8, more ....

Thanks again !!

Report •

August 31, 2010 at 08:56:52
Hmm - thought i had it cracked with your help, what i cannot get it to do now is repeat independently row by row, i.e.

1 2 3 (Nails)
1.1 7 6.2 (Screws)
3 .99 5 (Etc)....

etc..... where A1, A2 and B3 would in the above example CF green as the cheapest

Report •

August 31, 2010 at 09:24:59
Per this site you essentially have an unlimited number of Conditional Format rules that can be applied in 2007. You should review the material at that site if you plan to use CF in 2007 to help you understand all of the new features available. It goes way beyond just "how many rules you can use".


Therefore, by expanding my suggested formula by adding 0's and moving the 1, you should be able use it for as many CF's as you need.

To Conditionally Format multiple rows with the same rules...

- Select the range you want to CF, e.g. A1:C3
- Follow the steps to CF, but change the formulas by dropping the $ next the Row references:


That way the Formula will automatically increment the Row number and CF the entire range, row by row.

Report •

August 31, 2010 at 15:58:23

I tried DerbyDad03's CF's with the $ signs dropped before the Row numbers, using one row, but with four columns and four CFs.

It worked fine - four colors - in orders of value.
Here was the first CF:
and the last:
Note the $ signs.

With this working for cells A1 to D1, I selected and copied cells A1 to D1,
then I selected cells A2 to D9 and did PasteSpecial... and selected Formatting in the 'Other Paste Options' group.

All 9 rows now independently color the four values in each row according to their rank.


Report •

September 1, 2010 at 03:27:37
Brilliant - many thanks!!!!

Report •

Ask Question