CODEWITHSUNDEEP
vbaexcel
jmaz
jmaz

Reputation: 527

Worksheet Change Event Fires Upon Function Update, but not Worksheet Change

My worksheet contains a cell that calls a function, which (naturally) returns a value. If the value is "Filter", then the cell font color is supposed to change. Prob is, it only changes when I refresh the function in the cell, not when there is a worksheet change anywhere else in the worksheet. I need the font color to change upon any worksheet change. Why is this not the case?

 Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False

'change font color to red for filter losses
If Target.Column = 2 Then
    If Target.Text = "Filter" Then
            Range("B" & Target.Row).Font.ColorIndex = 3 
    End If
End If

Application.EnableEvents = True
End Sub

I have also tried using worksheet calculate event, shown below, but this code does not work at all:

Private Sub Worksheet_calculate()
Application.EnableEvents = False

Dim cell As Range

For Each cell In Me.UsedRange.Columns("B").Cells
    If cell.Text = "Filter" Then
        Range("B" & cell.Row).Font.ColorIndex = 3
    End If
Next cell

Application.EnableEvents = True
End Sub

Upvotes: 2

Views: 1379

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35863

As follow up from comments, this one works:

Private Sub Worksheet_calculate()
    Application.EnableEvents = False
    On Error GoTo ErrorHandler

    Dim cell As Range, rng As Range

    Set rng = Intersect(Me.UsedRange, Me.Range("B:B"))
    If rng Is Nothing Then GoTo ExitHere

    For Each cell In rng
       If cell.Text = "Filter" Then
           cell.Font.ColorIndex = 3
       End If
    Next cell

ExitHere:
    Application.EnableEvents = True
    Exit Sub
ErrorHandler:
    Resume ExitHere
End Sub

I slightly changed your event-hadler to be more reliable.
Also you can add rng.Font.ColorIndex = 1 just before loop to reset previous font color.

Upvotes: 2

Related Questions