Need 0 to highlight red with con.format

[ukphoenix]

Its Mr Stupid kicking in again im afraid,

I just cant put my finger on it. I need a cell to highlight RED (via conditional formating) if the cell reads 0.

When using conditional formatting and entering, the cell is equal to 0, it turns all the cells red that are empty aswell. This is what i dont what.

The only cell i require to turn RED is that with a 0 (zero) in

[Jericho]

I'm rather new to this whole Excel experience and I'm sure there's a better way to do this but here's a method I used to get around this situation:

In the conditional formating change the "cell value" drop down to "specific text" and then in the next drop down box select "beginning with" then put 0 in the 3rd box. That should do the trick, or at least be a quick fix until a better solution comes along.

[ukphoenix]

Thanx for the effort Jericho but I must be using an older version as "Specific Text" is not an option under conditional formatting. I have only "Cell Value Is" or "Formula Is"

[Portuga]

Try in "formula is": (ajust your range as you need).

(if cell value equals 0)

Please Login or Register  to view this content.

If cell value contains 0 inside a text string

Please Login or Register  to view this content.

[ukphoenix]

Nope it still picks up an empty cell as a 0 (zero)

If anyone wants to try and sort it out the sheets are contained in the sample file attached.

(Sorry about the macro but this needs to stay)

[Portuga]

One workaround

Add as a first condition:
=a1="" and choose "white"

and in the second condition:
=a1=0

[ukphoenix]

I already have a 1st condition which needs to remain. Please check attachment to confirm

[Portuga]

Try:

=AND($C$6<>"";M6<>"";M6=0) as first condition and choose red
=AND($C$6<>"";M6="") as second condition and choose white
=$C$6="" as third condition and choose grey

[ukphoenix]

Try:

=AND($C$6<>"";M6<>"";M6=0) as first condition and choose red
=AND($C$6<>"";M6="") as second condition and choose white
=$C$6="" as third condition and choose grey

Tried this but the section in red throws up error in the formula. Also it looks as if your trying to only set the formatting on the 1 cell (M6). It will need to be used across all grey cells (F6 to O24) as figures are entered in all boxes at some point at which anytime a 0 may occure

[Portuga]

You are probably getting an error because your excel version uses "," instead of ";"

Obviously, that formula will work for cell M6. You can THEN use the format painter to format the range you want. (watch for the cell reference- use the dollar sign as apropriate)

maybe something like this

=AND($C6<>"",M6<>"",M6=0)

[ukphoenix]

Thanx Portuga....finally got it sorted thanx to your formula. Dont know why but I thought the solution would have been easier than that.

Gone are the old days of A=0 then ""