Share this content

Conditional formatting woes

Conditional formatting woes

Didn't find your answer?

What annoys me is I've done this before, so I obviously know how to do it somewhere in my head...

I have an excel sheet.  We'll keep it simple, 5 columns 5 lines.

I want to use CF to make it so when B1 has an entry the whole row is green, if C1 has an entry the whole row is yellow, etc...

I can do that, no problem, highlight the whole row and then do the CF'ing.

I now want to do it for the other 4 rows without having to go through and set up more CFs, so I highlight the entire worksheet and go into CF, set it up as before... now the whole sheet changes colour if I enter something in B1, nothing happens if I enter into B2.  Not what I want.

Ok, maybe it is to do with relative references, so I remove the $ from the forumla - nope, now only a single cell changes colour.

I want it so if I put something in E3, all of 3 changes colour and nothing else.  I know it can be done (with one CF for each column, so in my case 5), but I can't see how...


Replies (2)

Please login or register to join the discussion.

By Manwithnoname
12th Mar 2015 15:52

Sorted!  Though I'm interested why.

I made the cell reference $A1 rather than $A$1 or A1 and it lets me copy it downwards, so that's all good.

Thanks (0)
Simon Hurst
By Simon Hurst
12th Mar 2015 19:06


Just to explain why it worked. The dollar sign 'fixes' the part of the cell reference that it precedes. $A1 fixes the column reference but allows the row reference to change. So, if the formula is copied across columns it will stay referring to column A, because there is a dollar sign in front of A and if the formula is copied up or down, the row reference will change since there is no dollar sign in front of the row number, 1.

Thanks (0)
Share this content