Conditional Format entire Column 1

[shelby55]

Hi

I'm fairly new to Excel and I'm using 2002.

My worksheet has a column denoting months, baseline figure, target figure, actual figure for the month. The % difference between actual and target is the final column E.

I want the entire column E to be a conditional format indicating that if 0% to 5% then red, 5.1 to 9.9% then yellow and >9.9% be green.

I know how to do conditional formatting for single cell but I can't seem to get it to work selecting whole column - all help greatly appreciated. Thanks.

 

[GlennUK]

It's exactly the same. Once you've selected the whole column, enter the conditions as you would for a single cell.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

hi,

I know how to do conditional formatting for single cell but I can't seem to get it to work selecting whole column

1. Select all cells you want to apply the CF to.

2. Use Formula Is: not Cell Value is:

3. CF using the cell value in the FIRST row of the selection. Make sure that you have the appropriate Absolute/Relative reference.

Your CF will be applies to each cell/row in the selection.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shelby55]

Hi

Thanks for the speedy replies. I'm confused by the absolute/relative reference.

For example cell E4 will be =(B4-D4)/B4 and cell E5 will be =(B5-D5)/B5 etc. so how does that translate into what I'm trying to do?

Thanks.

 

[SkipVought]

if E4 is the FIRST cell in your range AND the cells in column E are the ONLY cells being CFed, then...
[tt]
Condition1: RED =(B4-D4)/B4<=.05
Condition2: YELLOW =(B4-D4)/B4<=.099
Condition3: GREEN =(B4-D4)/B4<.099
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[GlennUK]

Hey Skip,

if the calculations are already in the cells, surely using "Cell Value Is" is better than using "Formula Is"? Or am I getting confused?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

Probably, but I almost always use formula is.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Also, I was not sure if the OP meant that the CELL VALUE has the formula or that formual was what the OP was using in the CF.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shelby55]

Hi

Thanks everybody...I did as Skip suggested to use the "formula" while selecting the whole column. Originally it wasn't working but then I saw Skip's comments about using 0.99 - I was using the whole number which is why it wasn't working! So now it's good - thanks again!!

 

[shelby55]

Hi

Sorry but one more thing: how can I get it so that the conditional formatting doesn't occur where no data is in the cell?

Right now it shows green for all the cells without entries. Thanks.

 

[SkipVought]

do this for each, assuming that E4 has your percentage value...

=AND(E4<=.05,not(isblank(E4)))
=AND(E4<=.099,not(isblank(E4)))
=AND(E4>.099,not(isblank(E4)))

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[gigahertz]

Format one cell then copy and "paste special" "formats" to other cells.

 

[SkipVought]

gigahertz as suggested yet another means of propogating the CF.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]