Highlighting a cell if the value is 0

DSLA

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

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

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

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
301
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
56,563
Office Version
  1. 365
Platform
  1. Windows
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
301
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
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
56,563
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.
 

Forum statistics

Threads
1,176,070
Messages
5,901,208
Members
434,882
Latest member
tchien69

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top