VBA Conditional Formatting

[Dokat]

Hi,

I have a worksheet name "HDD" and i want to change the color of the values to red when they are below 0. I use below worksheet change function however it's not changing the txt color to red. Can anyone help? Thanks

Private Sub Worksheet_Change(ByVal Target As Range)


For Each thecell In Target
    If (Not IsEmpty(thecell)) Then
        With thecell
            If Sheets("HDD").Range("A:ZZ").Value < 0 Then
                Sheets("HDD").Range("A:ZZ").Font.Color = RGB(255, 0, 0)
            ElseIf .Value > 0 Then
                Sheets("HDD").Range("A:ZZ").Font.Color = RGB(0, 255, 0)
            Else
                Sheets("HDD").Range("A:ZZ").Font.Color = RGB(255, 255, 255)
            End If
        End With
    Else
        Sheets("HDD").Range("A:ZZ").Font.Color = RGB(255, 255, 255)
    End If
Next
End Sub

 

[Marc L]

Hi !

Did you ever try to change a cell value within HDD worksheet ?
That's all we can answer without an attachment as per forum rules …
​

 

[Dokat]

Yes i did. Still no luck. Attached is the sample file. Code is on the bottom. Thanks

 

[NARAYANK991]

Hi ,

A Worksheet_Change procedure is an event procedure , which means Excel will run it automatically , when ever it detects an event ; in the case of this procedure , the event is a change in any worksheet cell by the user.

For this to happen however , the procedure has to be placed in the worksheet where the changes will take place and need to be detected ; it cannot be placed in a code module.

Narayan

 

[NARAYANK991]

Hi ,

Secondly , the Worksheet_Change procedure will run when ever a change takes place in any worksheet cell due to user input.

Generally speaking , the Worksheet_Change procedure should react to the event , and not operate on the entire worksheet or even worse , some other worksheet. That is the reason the procedure has an input parameter named Target ; this tells the procedure which cell has experienced the change.

If , for any reason , a Worksheet_Change procedure needs to change cells in the worksheet where it is placed , you should enclose such statements between the following two statements :

Application.EnableEvents = False

Application.EnableEvents = True

Otherwise , the procedure will call itself and an infinite loop will result , often crashing or freezing Excel.

Narayan

 

[Dokat]

Hi,

Oh yes it makes sense, thanks for the help. I put it on the worksheet code but i am still getting "Run Time Error 9" Subscript out of range. Do you know what may be causing this. Thanks

Private Sub Worksheet_Change(ByVal Target As Range)

    Set MyPage = Sheets("HDD").Range("A2:ZZ")

    For Each cell In MyPage

        Select Case cell.Value

         Case Is < 0

            cell.Font.Color = RGB(255, 0, 0)
           
         Case Is > 0

            cell.Font.Color = RGB(0, 0, 0)
       
        Case Else
            cell.EntireRow.Interior.ColorIndex = xlNone

        End Select

    Next

End Sub

 

[NARAYANK991]

Hi ,

The following statement will generate an error :

Set MyPage = Sheets("HDD").Range("A2:ZZ")

You need to add a row index to the ZZ reference.

Narayan

 

[Dokat]

Thank you. Yes i added a index row number to ZZ and added "Application.EnableEvents = False" and "Application.EnableEvents = False" but excel still keep crashing and freezing

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

    Set MyPage = Worksheets("HDD").Range("A2:ZZ3955")

    For Each cell In MyPage

        Select Case cell.Value

         Case Is < 0

            cell.Font.Color = RGB(255, 0, 0)
           
         Case Is > 0

            cell.Font.Color = RGB(0, 0, 0)
       
        Case Else
            cell.EntireRow.Interior.ColorIndex = xlNone



        End Select

    Next
Application.EnableEvents = True
End Sub

 

[NARAYANK991]

Hi ,

The problem is the number of cells in your range !

Column A through Column ZZ is 702 columns , and with 3955 rows , the code is going to check nearly 3 million cells !

Narayan

 

[Marc L]

As a starter :​

Private Sub Worksheet_Change(ByVal Target As Range)
         Dim Rg As Range
    For Each Rg In Target
             Rg.Font.Color = IIf(Rg.Value < 0, vbRed, vbBlack)
    Next
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Marc L]

As this kind of code is useless just using cell format !​

 

[Dokat]

Thank you both. Once i narrowed the range it worked and Marc L code works very fast.

 

[Marc L]

Dokat thanks for the like, I just mod my code.

Just note you do not ever need this code just with cell format
as you can read within Excel inner help …
​

 

[Dokat]

Thanks for the insight