Highlighting a cell if the value is 0

[DSLA]

Hello (again),

I need to highlight a cell if the value is 0 (zero), how can i do that?

 

[MarkAndrews]

Format > Conditional Formatting

Cell Value is > Equal to > 0 > Select colour

HTH

 

[DSLA]

It tried it but it's not working.

I have a IF Function in the cell i need to highlight, and it doesn't seem to work with that. I trien the same with a cell with no IF function and it worked normally.

 

[MarkAndrews]

Change Cell Value Is to Formula is in the conditional format window

Paste in your formula

Although it should work, I've done something recently where the result of a cell is via an IF statement, and it worked fine?

This was mine

Highlights if Error shows (in red fill)

=IF('Criteria Selection & Pivots'!$B$38='Criteria Selection & Pivots'!$B$9,'Criteria Selection & Pivots'!$B$38,"Error")

 

[Peter_SSs]

It tried it but it's not working.

Can you explain how it is not working? eg No colour shows when the cell is zero. Or the cell is coloured whatever its value. etc

I have a IF Function in the cell i need to highlight, and it doesn't seem to work with that. I trien the same with a cell with no IF function and it worked normally.

Can you tell us what cell it is (eg B4)?
Can you paste your IF formula here?

 

[DSLA]

Here is the formula:

=IF(ISNA(VLOOKUP(A35;EXCELSHEET!C1:N200;8;FALSE));"0";VLOOKUP(A35;EXCELSHEET!C1:N200;8;FALSE))

 

[sam slade]

Hi,

may i add a vba solution that you detect a change in the worksheet, check the target column, then cycle through all cells checking if the value is 0 and highlight it accordingly.

just a different spin i guess. Although would be slow if worksheet rowcount was extensive.

Sam

 

[Peter_SSs]

Here is the formula:

=IF(ISNA(VLOOKUP(A35;EXCELSHEET!C1:N200;8;FALSE));"0";VLOOKUP(A35;EXCELSHEET!C1:N200;8;FALSE))

OK, so the 0 is actually "0" which makes it text not a number. You should be able to go right back to MarkAndrews' first suggestion of Conditional Format with Cell Value|Is equal to|="0" and set your colour.

edit: Or change your formula to:
=IF(ISNA(VLOOKUP(A35;EXCELSHEET!C1:N200;8;FALSE));0;VLOOKUP(A35;EXCELSHEET!C1:N200;8;FALSE))
and use Mark's original suggestion.

 

[DSLA]

Thanks for the help guys, i've got it working.