VBA Excel help Colour a cell that contains 0

Closed
clairebear292 Posts 2 Registration date Wednesday August 8, 2018 Status Member Last seen August 8, 2018 - Aug 8, 2018 at 02:47 AM
vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 - 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!
Related:

2 responses

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 Status Member 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
0
vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Aug 9, 2018 at 03:53 AM
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.