VBA Excel help Colour a cell that contains 0 [Closed]

Report
Posts
2
Registration date
Wednesday August 8, 2018
Status
Member
Last seen
August 8, 2018
-
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
-
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!

2 replies

Conditional Formatting will work. Make a formula for ALL cells that contain 0, formatted to RED.



Posts
2
Registration date
Wednesday August 8, 2018
Status
Member
Last seen
August 8, 2018

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
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
212
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.