Excel 2007 : Simple conditional format but.??

[Rob44]

Hi,

Another crazy formula that doesn't do what I want.

Conditional format a column, highlight the cell with 1 in it but it doesn't it highlights the blank cell.

I tried it both ways round but still get the same result, blank cell highlighted.

=AND(C2=1,C1="")

I'm almost ashamed to be asking this it looks so simple but I'm obviously not aware of something...

[Richard Buttrey]

Hi,

Just swap the tests around. i.e.

Please Login or Register  to view this content.

[Rob44]

Richard...

The blank cell will be above the cell with 1 in it.

I did try.....

=AND(C1="",C2=1)

But both ways it still highlights the blank cell...

[daddylonglegs]

Try using your original formula, i.e.

=AND(C2=1,C1="")

but don't select the whole column, just select C2:C100 or similar

If you want to format the whole column you'll have to refer to the last cell of column C, i.e.

=AND(C1=1,C1048576="")

[Richard Buttrey]

Hi,

If I understand your requirement then

In C1
Cond format 1
Cell Value is equal to 1 format as appropriate

Cond format 2
Formula Is: =C2=1 format no colour

HTH

[Rob44]

Richard,

As the formula states....if C1 is blank and C2 is equal to 1.... highlight the cell, but the formula highlights the blank cell not the cell with the 1 in it and i want the cell with the 1 highlighted (C2).

daddylong legs... tried...C2:C100....no difference......

=AND(C1=1,C1048576="") tried that and it just poked a random highlight in a cell with a 2 in it (C32)...

[daddylonglegs]

...but you are formatting the whole column, is that right? So what's the general rule, do you want to highlight any cell that has a 1 and where the cell above has a blank?

If you assign a format to a whole column then remember that the condition you actually put in is the one you want to apply to the first cell in the column so if you select column C and use the formula

=AND(C2=1,C1="")

Then that's the formula that will apply to C1. C1 will be formatted if C2 = 1 and C1 is blank....so that's what you got, C1 was formatted when C1 was blank and C2 =1

That's why you need to apply that formula to C2

then C2 will be highlighted when C2 =1 and C1 is blank.......that pattern will be replicated down the column to give your general rule, any cell will be highlighted as long as it has a 1 in it and the cell above is blank

[Rob44]

daddylonglegs...That's exactly what I'm trying to do.....The conditional format is as I have said and it is to format the whole column (C:C) but it highlights only the blank cells above the ones with a 1 in it. I want it to highlight the cell with a 1 in it provided the cell above it is blank...

[Rob44]

I've attached a sheet to show what i've got and what I want.

[daddylonglegs]

OK I just did what I suggested above. In sheet 2 I selected the whole column C and applied the formula

=AND(C1=1,C1048576="")

In sheet 1 I selected a range beginning at C2, C2:C30, and then applied this formula.

=AND(C2=1,C1="")

both work - as you can see all 1s are formatted except for those with a non-blank cell above

[daddylonglegs]

I didn't see your last post when I posted mine - here it is applied to your sheet.....

[Rob44]

daddylonglegs...I think I owe you an apology.....

=AND(C1=1,C1048576="") did work....

I did try that before, as you suggested, but must have done something wrong.

Thank you for your persistance.....

[daddylonglegs]

No problem, happy to help.......

[Rob44]

I just tried the other way of doing it, as you explained and found out why it didn't work for me the first time.
When I applied it to C2:C600 for some reason the formula changed from......

=AND(C2=1,C1="")

to

=AND(C3=1,C2="")

Would this be normal.....?