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...

Frustrated...

Replies (2)

Please login or register to join the discussion.

avatar
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

$s

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)