Highlighting a cell if the value is 0

DSLA

Active Member
Joined
Jun 6, 2005
Messages
296
Hello (again),

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

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
Format > Conditional Formatting

Cell Value is > Equal to > 0 > Select colour

HTH
 

DSLA

Active Member
Joined
Jun 6, 2005
Messages
296
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

Well-known Member
Joined
May 2, 2006
Messages
1,963
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)

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,786
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Jun 6, 2005
Messages
296
Here is the formula:

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

sam slade

New Member
Joined
Sep 14, 2006
Messages
22

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,786
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,937
Messages
5,543,093
Members
410,583
Latest member
gazz57
Top