Conditional Formating across Multiple Cells

[RichardWatson]

Hi Guys,

My 2nd question of the day!

I want to use Conditional Formating to change the font colour to either red or green depending on a certain value in corresponding cells.

for example if A3 is greater than A1 the font should be Green (or red if lesser than) I wan tthis to be the same through B3 and H3.

Is there a simple way of doing this or do I have to make the individual formating for each cell?

Thanks again!

 

[Marcol]

Select the range A3:H3
Then Home > Conditional Formatting > New rule ...

Select Use a formula ....
Enter this formula
=A3>$A$1
Then format... Fill > Green

Repeat with
=A3<$A$1
Then format... Fill > Red

Hope this helps

 

[Peter_SSs]

You haven't said what should happen if the cells are equal.

It is also not clear if you are comparing B3 to B1, C3 to C1 etc or whether you are comparing A3, B3, C3 etc all to A1. This is for comparing each cell in row 3 to the corresponding value in row 1.

First I selected A3:H3 and used normal cell formatting to format the font red. Then, with C3:H3 still selected, I applied the Conditional Formatting shown.

Excel Workbook
	A	B	C	D	E	F	G	H
1	2	8	9	24	6	4	15	5
2
3	5	8	4	20	8	9	6	55
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3	1. / Formula is =A3>=A1	Abc

 

[RichardWatson]

You haven't said what should happen if the cells are equal.

It is also not clear if you are comparing B3 to B1, C3 to C1 etc or whether you are comparing A3, B3, C3 etc all to A1. This is for comparing each cell in row 3 to the corresponding value in row 1.

First I selected A3:H3 and used normal cell formatting to format the font red. Then, with C3:H3 still selected, I applied the Conditional Formatting shown.

Excel Workbook
	A	B	C	D	E	F	G	H
1	2	8	9	24	6	4	15	5
2
3	5	8	4	20	8	9	6	55
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3	1. / Formula is =A3>=A1	Abc

it will never be equal, always greater or lesser than.
C1 through to C5 will be greater or lesser than A1 through A5.

 

[Peter_SSs]

C1 through to C5 will be greater or lesser than A1 through A5.

Seems like we have changed from comparing Row 3 v Row1 to comparing Column C v Column A. So, in a similar vein ..

1. Select C1:C5 and apply red font.

2. While the range is still selected, apply the CF shown.

Excel Workbook
	A	B	C
1	2		5
2	8		10
3	9		4
4	24		20
5	6		8
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	1. / Formula is =C1>A1	Abc

 

[RichardWatson]

Seems like we have changed from comparing Row 3 v Row1 to comparing Column C v Column A. So, in a similar vein ..

1. Select C1:C5 and apply red font.

2. While the range is still selected, apply the CF shown.

Excel Workbook
	A	B	C
1	2		5
2	8		10
3	9		4
4	24		20
5	6		8
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	1. / Formula is =C1>A1	Abc

why am I aplpying the red font?
I want the "conditional format" to determin the font colour. I cna do this in the individual cell but want to be able to apply it to various cells at the same time.

what you have suggest doesnt make sense to me.

 

[Peter_SSs]

why am I aplpying the red font?
I want the "conditional format" to determin the font colour.

You can apply the red colour by CF as well if you really want but CF is volatile and adds to the 'overhead' for the sheet. It isn't going to make a noticeable difference if you are only using CF in 5 cells but it just seems sensible to me to not use CF any more than you need.

My logic is this. You want cells either red or green. If you make themn all red by default (that is the normal formatting I suggested) and then make the 'bigger' ones turn green with CF, the locical conclusion is the the remaining ones must be the 'small' ones and remain red.

I cna do this in the individual cell but want to be able to apply it to various cells at the same time.

If you read and follow my directions, all the cells will be done at once.

Does my sheet look correct for my sample data?

what you have suggest doesnt make sense to me.

Hopefully it makes more sense now. In any case, did you try it?

If you did/do and it does not work as you would want, please describe your sample data, what my suggestion did and where it went wrong and I will try to modify it.

 

[RichardWatson]

You can apply the red colour by CF as well if you really want but CF is volatile and adds to the 'overhead' for the sheet. It isn't going to make a noticeable difference if you are only using CF in 5 cells but it just seems sensible to me to not use CF any more than you need.

My logic is this. You want cells either red or green. If you make themn all red by default (that is the normal formatting I suggested) and then make the 'bigger' ones turn green with CF, the locical conclusion is the the remaining ones must be the 'small' ones and remain red.


If you read and follow my directions, all the cells will be done at once.

Does my sheet look correct for my sample data?

Hopefully it makes more sense now. In any case, did you try it?

If you did/do and it does not work as you would want, please describe your sample data, what my suggestion did and where it went wrong and I will try to modify it.

ahhh I get the choose red now! good idea, makes for less formating!
but I wont be doing this for just 5 cells that was just my example it will be about 200.

When I try to apply the CF through muptiple cells with 2 rules (one for greater and one for lesser) all of the cells turn out with the same colour font, regardless of the sum outcome.

 

[RichardWatson]

I have just done it the way you have suggest too and all of the cells remain with a red coloured font!!

 

[Peter_SSs]

ahhh I get the choose red now! good idea, makes for less formating!
but I wont be doing this for just 5 cells that was just my example it will be about 200.

200 cells shouldn't be a problem. Just select them all before applying the CF as if you are just applying it for the first one. Or just apply the first cell and use the Format Painter to apply to the other 199 or whatever.

When I try to apply the CF through muptiple cells with 2 rules (one for greater and one for lesser) all of the cells turn out with the same colour font, regardless of the sum outcome.

What 'sum' are you talking about? If you told us what your sample data was, where it is, what your CF rules are and what you are trying to achieve we might be able to advise why it is not acting as you expect. Without seeing what you have done, it's hard to diagnose.