VBA Excel help Colour a cell that contains 0

clairebear292 2 Posts Wednesday August 8, 2018Registration date August 8, 2018 Last seen - Aug 8, 2018 at 02:47 AM - Latest reply: vcoolio 1194 Posts Thursday July 24, 2014Registration dateModeratorStatus September 8, 2018 Last seen
- 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 10480 Posts Monday June 3, 2013Registration dateModeratorStatus September 18, 2018 Last seen - 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 2 Posts Wednesday August 8, 2018Registration date August 8, 2018 Last seen - 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 1194 Posts Thursday July 24, 2014Registration dateModeratorStatus September 8, 2018 Last seen - 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