VBA Excel help Colour a cell that contains 0

clairebear292
Posts
2
Registration date
Wednesday August 8, 2018
Last seen
August 8, 2018
- Aug 8, 2018 at 02:47 AM - Latest reply: vcoolio
Posts
1204
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 11, 2018
- Aug 9, 2018 at 03:53 AM
Hi,

I have a table that displays data from another tab using VLOOKUP, although i have some cells that contain a 0

I have tried using conditional formatting to colour the cells that contain the 0 but it doesn't work.

I thought i would try writing a VBA that highlights the 0 cells when i open the document.

Although I'm not having much luck with the info i have found online?

Table range F5:U19
Colour the Cells that contain a 0 in red

Any help would be appreciated!
See more 

Your reply

3 replies

ac3mark
Posts
10830
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
November 13, 2018
- Updated by ac3mark on 8/08/18 at 05:40 PM
0
Thank you
Conditional Formatting will work. Make a formula for ALL cells that contain 0, formatted to RED.



clairebear292
Posts
2
Registration date
Wednesday August 8, 2018
Last seen
August 8, 2018
- Aug 8, 2018 at 07:02 PM
Thanks for your reply but I have tried it again and still doesn't work :(
I have tried changing the format to text, general, number etc and no luck
Respond to ac3mark
vcoolio
Posts
1204
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 11, 2018
- Aug 9, 2018 at 03:53 AM
0
Thank you
Hello Clairebear292,

Where in this Great Southern Land are you hiding?

Try the following code placed in the worksheet module:-

Private Sub Worksheet_Activate()

Dim c As Range

For Each c In Range("F5:U19")
      If c.Value = 0 Then
      c.Interior.ColorIndex = 8
      End If
Next c

End Sub


The code is a Worksheet_Activate event code so once you open the relevant sheet, the code will execute.

To implement the code:-

- Right click on the relevant sheet tab.
- Select "View Code" from the menu that appears.
- In the big white field that then appears, paste the above code.

You can change the ColorIndex to suit yourself.

Once you enter the code, you'll then need to save the workbook with the .xlsm file extension.

I hope that this helps.

Cheerio,
vcoolio.
Respond to vcoolio