EXCEL Conditional Formatting

  westwit 11:54 24 Jun 2006

Using Excel 2003 I know how to do conditional formatting on one cell, but is it possible to format several cells using the value of just one. I.e. is it possible to make a range of cells in a row switch to red font when one cell in the row has the value "PAY IN"?

  Noldi 12:02 24 Jun 2006

Yes no problem
Select all the cells you need to format then condition format - Formula is.

  johnnyrocker 13:47 24 Jun 2006

or use the 'if' formula


  Simsy 14:15 24 Jun 2006

for Excel 2000

Assuming the cell with "Pay in" is A1

select the cells you want to be formatted..

Format>Conditional formating

select "Formula is" and enter

=$A$1="Pay in"

Choose your format.



  westwit 17:29 24 Jun 2006

Thanks to all. That does one row. Now how do I "fill down" this conditional formatting so that the next row refers to A2, the third one to A3 etc? When I try to copy the formatting down several rows the cell ref remains as A1; if I try to enter the cell ref without the $ signs it doesn't work at all. If I fill in two rows manually, changing the second cell ref to $A$2 then select several rows and try "fill>series>step"1" nothing happens!

I'm sure I must be able to do this, so what am I missing?

  VoG II 17:32 24 Jun 2006


=$A1="Pay in"

  westwit 17:37 24 Jun 2006

Thank you very much VoG™; works a treat now just dragging the formatting down. Why is it that you know so much and the rest of us (me in particular) don't? I suppose at least I know where to ask!

  Simsy 22:47 24 Jun 2006

the $ symbol in the formula keps the reference "absolute", and is/can be used for both the row and column reference...

Your original question asked "but is it possible to format several cells using the value of JUST ONE"

That's why I put the $ in front of both the A, (the column), and the 1, (the row).

Hope this helps. The $ symbol is often useful in this way.



  westwit 14:46 28 Jun 2006

I see now that the row must not be absolute or the filling down can't work. But why does conditional formatting of a range of cells not work without at least one absolute reference? I tried without the $ (i.e. just A1) in order to get the fill down, but the conditional formatting wouldn't take.



  VoG II 14:52 28 Jun 2006

If you just wanted to fill down one column then using the relative address A1 should work.

However, as you want to format a whole row, then the $A1 is necessary such that each cell in that row is 'looking' at A1. If you set a conditional format in B1 that referenced A1 and tried to copy the formatting across the row then C1 would be 'looking' at B1, D1 at C1 etc. and the formatting would not work as expected.

  Simsy 06:53 29 Jun 2006

a little confused; you're talking about a "row", but also refer to filling "down"? When you say "row" do you mean "column"?

Anyway... lets say you have something in A1, and you format cell B2 based on the contents of A1, say using the formula =A1>0 ...

As the formula uses "A1", (no $ signs), and you copy the format down column B, then the conditional format formula for cell B2 will be;


i.e. the formating for cell B2 is based on the condition of cell A2. This is the same as the formatting in cell B1, in as much as it's formatting is based on the condition of the cell immediately to it's left, (the same as for cell B2)... This is "Relative". There are no $ signs.

If the formula used in the formating for cell B1 was =A$1>0, and you then copy the format down column B, the formula for formatting in B2 would also be =A$1>0 By using the $ sign in front of the 1 you tell the formatting "stay with this row". If you use the $ sign in front of the A, you are telling the formatting, "stay with this column". If you use the $ sign in front of both you are telling it to stay with botht the row and the column... in other words, "stay with this cell" This is "absolute".

Does this help?



This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5T review: Hands-on

Illustrator Andrés Lozano on his improv line work, brazen use of colours & hand sketching

iPhone X review

Comment envoyer gratuitement des gros fichiers ?