VBA Excel help Colour a cell that contains 0 Closed

Question

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!

ac3mark · Answer

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

vcoolio · Answer

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.

VBA Excel help Colour a cell that contains 0

2 responses

Similar discussions

Subscribe To Our Newsletter!