How to adapt event SelectionChange code to work in event Calculate?-CodePudding

The below code works on event SelectionChange without any problem at all. But, if I moved to it event Calculate , this error appear Method 'Undo' of object '_Application' failed . There is another code on Worksheet_Change , But I think it is not the cause of problem.

Private Sub worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Cells.CountLarge = 1 Then
Dim Column_A As Range
 Set Column_A = ActiveSheet.Range("A3", ActiveSheet.Range("A" & ActiveSheet.Rows.count).End(xlUp))
 If ActiveSheet.FilterMode = True Then
    Column_A.Interior.Color = RGB(255, 0, 0)
    Else  'FilterMode = False
    Column_A.Interior.Color = RGB(255, 255, 255)
  End If
  End If
End Sub

I tried to use If Target.Cells.CountLarge = 1 in event Calculate , But this error raised Object required As always, Great thanks for all your support

CodePudding user response：

In reading your post and most recent comment, I can't work out (with any certainty) what you're actually trying to do. Taking a best-guess approach, I've put together the following. It takes action in the Calculate event only when the value of cell AL1 changes as part of that Calculate.

Option Explicit

''' Reference value register
    Private mvnRefValue

''' Initialise Reference value when sheet first activated
Private Sub Worksheet_Activate()
    If mvnRefValue = Empty Then mvnRefValue = Range("AL1")
End Sub


Private Sub Worksheet_Calculate()

    Dim Column_A As Range

If Range("AL1") <> mvnRefValue Then
    
    With ActiveSheet
        Set Column_A = .Range("A3", .Range("A" & .Rows.Count).End(xlUp))
    End With
    
    If ActiveSheet.FilterMode = True Then
        Column_A.Interior.Color = RGB(255, 0, 0)
    Else  'FilterMode = False
        Column_A.Interior.Color = RGB(255, 255, 255)
    End If

''' Update mvnRefValue
    mvnRefValue = Range("AL1")
End If

End Sub

If you want to test for a more more complex change scenario, adopt the notions used above as appropriate (and/or explain more fully what if is you're actually trying to do).