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