MrExcel.com - Your One Stop for Excel Tips & Solutions

Using VBA to make a conditional format so the "0" are always white

Posted by TIM on June 26, 2001 2:52 AM

I have used up all three of the conditional formats and would like to add a 4th... but through using VB if possible.

What i would like to is is make all of the zero values white...

Cheers

Tim

P.s

only zero values and not all 0 i.e 0 = yes || 12,000 = normal

Posted by Dax on June 26, 2001 4:36 AM

Hello Tim,

If you right click the worksheet tab and choose View Code. Then use this code.

Private Sub Worksheet_Activate()
ApplyFormatting UsedRange
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
ApplyFormatting Target
End Sub

Sub ApplyFormatting(rnge As Range)
For Each cl In rnge.Cells
If cl.Value = 0 Then
cl.Font.Color = vbWhite
Else
cl.Font.Color = vbBlack
End If
Next
End Sub

When the sheet is activated the formatting will be applied to the whole sheet. After that, the formatting will only work on any changes made by the user. This will help speed up execution time.

HTH,
Dax.

Posted by JAF on June 26, 2001 4:47 AM

Always explore the simple option...

Hiya

a much simpler method which will achieve the same result would be to set Excel so that zero values are not displayed.

To do this, go to Tools/Options and on the View tab, uncheck the Zero Values option.

Doing this means that cells containing a value of zero are "blank", and so will achieve the same result as formatting in white.


JAF

Posted by Dax on June 26, 2001 5:01 AM

Re: Always explore the simple option...

Who said that his background was white? It probably is though!

Regards,
Dax.