For Loop does not seem to be working

[sllaksvb]

Hi all

What I am trying to achieve is when someone enters a value into cells(4,5) to (4,7), cells (3,5) to (3,7)'s font will go white, respectively.
I wrote this for loop, but it does not seem to be working. This is my code:


Private Sub Worksheet_Change(ByVal Target As Range)

For x = 5 To 7
If Sheets("Buffers & Overrides").Cells(4, x) > 0 Then
Sheets("Buffers & Overrides").Cells(3, x).Font.Color = RGB(255, 255, 255)
ElseIf Sheets("Buffers & Overrides").Cells(4, x) = 0 Then
Sheets("Buffers & Overrides").Cells(3, x).Font.Color = RGB(0, 0, 0)
End If
Next x

End Sub

Any help or comments is greatly appreciated!

[SamT]

Excel Tools Menu >> Options. On the View tab, uncheck "Zero Values."

Alternately; Select the relevant cells then on the Excel menu, Format >> Conditional Formatting, set "Less than or equal to," and change the formatting to white font.

Note: The first works only on zero's and affects the entire Worksheet, the second works on zero's and on negative numbers, and only on the selected range, but you have many, many options with Conditional Formatting.

[sllaksvb]

Hi SamT,

Thank you for your reply! I'm not sure I entirely understand your advice.

I'm not too sure where to locate the "Zero Values" checkbox on the view tab. Also, I tried with conditional formatting, however it only lets me format the cell that I selected (e.g. if cell A4 > 0, make the font white.) However, it does not allow me to format a different cell based on the rules.

Is there a way I could use VBA to achieve my objective?

[offthelip]

Conditional formatting does allow you to change the format depending on the value in another cell, select cell E3 then conditional formatting/ New Rule/ Use a formula to determine which cells to format, then in the box where is says Format values where this formula is true, type in =E4>0, then select the format that you want appplied for this condition whichI presume is a white fill. You can then copy the format across the other cells.

[NM91]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Row
Case 3, 4
If Target.Column > 4 And Target.Column < 8 Then
If Target.Value > 0 Then Target.Font.Color = RGB(255, 255, 255) Else Target.Font.Color = RGB(0, 0, 0)
End If
Case Else
'Target.Font.Color = RGB(0, 0, 0)
End Select
End Sub

[mdmackillop]

As an alternative: to hide content, I prefer Numberformat = ";;;"

[sllaksvb]

offthelip, thank you for breaking it down for me! I'm not too experienced with the functionalities of Excel yet, really appreciate your help!

NM91, thank you for your code!

mdmackillop, may I ask how to unhide if I am using the numberformat=";;;" code?

[mdmackillop]

mdmackillop, may I ask how to unhide if I am using the numberformat=";;;" code?
Change to General or whatever you need for displaying the cells as you wish.

[sllaksvb]

mdmackillop, thank you for your advice! Changed all the font.color properties to numberformat and it works perfectly.