Changing colour depending on value

**Paul Cooke** · 12-01-2007, 01:06 PM

Hi Could someone advise me if it is possible to change the colour of a cell text and / or cell background depending on a value.

For example I have currently 2 cells with numbers in
G3 = 4.38
K3 = 8.68

What I wolud like to happen is for the cell with the lowest value to change to Red.

The Column letters will always remain the same but I do plan to add a lot more rows.

Many thanks for any advice you can give on this

Paul

**daddylonglegs** · 12-01-2007, 01:24 PM

Select G3 and use

Format > Conditional Formatting > Select "Formula is" from the dropdown and use this formula

=G3=MIN(G3,K3)

choose red format

Do the same for K3 except change formula to

=K3=MIN(G3,K3)

If you want to avoid the formatting being applied when both cells are blank change G3 formula to

=(G3=MIN(G3,K3))*(G3<>"")

and similar for K3

**Ron Coderre** · 12-01-2007, 01:29 PM

Conditional Formatting should give you what you want...

Select cell G3

From the Excel Main Menu
<format><conditional formatting>
Formula is: =AND(COUNT($G3,$K3)=2,(G3=MIN($G3,$K3)))
Click the [Format] button and set the colors you want.
Click [OK] to finish

Note: If both cells in the same row contain the same value,
that CF highlights both cells. However, if either cell is blank
neither cell is highlighted.

Copy G3 to K3
Copy G3 down as far as you need
Copy K3 down as far as you need

Does that help?

**Paul Cooke** · 12-01-2007, 01:51 PM

Many thanks Daddylonglegs for the very quick response. I have tried your code first and it does work

I formatted it to change the cells to Red & Bold

There is one small problem though !!

If the value in K3 changes to a value less than G3, K3 does automatically change to Red & Bold but G3 does not change colour back to black it only loses the bold bit and the text is still red?

Any ideas?

Thanks again

Paul

**Paul Cooke** · 12-01-2007, 01:57 PM

Hi Ron thank you to you too for a very quick response.

This does change cell G3 to Red & Bold but when i copy it to K3 that also changes to red and bold and G3 remains red & Bold. What I need is for the cell with the lowest value to change

Does this make sense?

Thanks again

Paul

**Ron Coderre** · 12-01-2007, 02:14 PM

Unless you want NEITHER cell colored when there is a tie,
then I don't understand.

With the CF that I posted.

G3: 5.......not highlighted
K3: 4.......Highlighted

G3: 4.......Highlighted
K3: 5.......not highlighted

G3: 4.......Highlighted
K3: 4.......Highlighted

What am I missing?

**Paul Cooke** · 12-01-2007, 09:03 PM

Hi Ron thanks again for the reply

What I am doing is a small spread sheet of supplier costs for good we buy.
In Column G is the actual cost we pay for goods from supplier 1 and column K the same information from supplier 2.

As you wrote in your last reply I need either the G cells or K cells to turn Red & Bold depending on which one has the lowest value.

After entering the formula in your first post into G3 it works fine and the cell changes to Red & Bold after this I have the following problems:

1 if i copy the formula by copying G3 to K3, the cell K3 turns to Red and G3 loses the Bold but stays Red

2 If I use the CF button to write the formula directly into K3 nothing changes
in K3 but G3 again loses the Bold.

3 If the value in K3 becomes lower than G3 noting changes in either of the cells I.E G3 stay Red and K3 Stays black.

Sorry if I am not explaing this properly I know what I mean in my head !!

I have attached a copy of the sheet for you to have a look at it may be easier that way. The only thing to note when you see it is that columns E & I (book cost) can be reduced or incresed at times

Hopefully this makes sense

Thank you very much for helping me

Paul

**Ron Coderre** · 12-01-2007, 10:09 PM

I think I identified the flaws in your worksheet.

Try this:

Copy G3 and paste to K3.
Select K3
.....<format><cells><Patterns tab>
.....set the color to match the column.

That should do it!
(does it?)

**Paul Cooke** · 12-01-2007, 10:33 PM

Hi Ron

Im so sorry but am I being very stupid here as I can't find a "patterns tab" any where in the format box although I actually understand how this will solve the problem !!?

Just to let you know I'm using Excel 2007 if that makes a difference

Thanks again

Paul

**Ron Coderre** · 12-01-2007, 11:05 PM

Originally Posted by Paul Cooke

Just to let you know I'm using Excel 2007 if that makes a difference

My condolences.

I don't use Excel 2007. Consequently, I don't know where to find the formatting options in it.

**Paul Cooke** · 12-01-2007, 11:23 PM

Yes I know what you mean - I'm starting to wish I never upgraded with that and to make matters worse my new laptop has vista on it

I'm really gratful for your help Ron, The basis of what I want is there I'm sure I can find out the colour format bit with a quick google (hopefully) !!

Many thanks again

Paul