Worksheet_calculate()
I'm usinin the following code to highlight the filtered column with a
different collar. To make it works I've entered =Now() in some cell. Everything is fine. But, when I'm opening another spreadsheet the procedure is being automatically triggered producing the error "out of range". Private Sub Worksheet_Calculate() Dim af As AutoFilter Dim fFilter As Filter Dim iFilterCount As Integer 'Application.EnableEvents = False If Worksheets("Initiatives").AutoFilterMode Then ' gettin an error "out of range" Set af = Worksheets("Initiatives").AutoFilter iFilterCount = 1 Worksheets("Initiatives").Unprotect ("donit") For Each fFilter In af.Filters If fFilter.On Then af.Range.Cells(1, iFilterCount) _ .Interior.ColorIndex = 3 ' 15 - gray; 35 - mint green Else af.Range.Cells(1, iFilterCount) _ .Interior.ColorIndex = 15 End If iFilterCount = iFilterCount + 1 Next fFilter Else Worksheets("Initiatives").Range("A1").AutoFilter Worksheets("Initiatives").Range("A1:H1").Interior. ColorIndex = 15 End If How could I fix it? Thanks |
Worksheet_calculate()
=Now() is a volatile function, meaning that it calculates every time that a
calculation is executed. If you can, try to find a way to replace the now function with a constant. You can update the constant base on some other event such as a sheet activate or workbook open or... Otherwise you can also add a criteria to the calculation event that ThisWorkbook is the active workbook. If it isn't then abort the rest of the procedure. -- HTH... Jim Thomlinson "Alex" wrote: I'm usinin the following code to highlight the filtered column with a different collar. To make it works I've entered =Now() in some cell. Everything is fine. But, when I'm opening another spreadsheet the procedure is being automatically triggered producing the error "out of range". Private Sub Worksheet_Calculate() Dim af As AutoFilter Dim fFilter As Filter Dim iFilterCount As Integer 'Application.EnableEvents = False If Worksheets("Initiatives").AutoFilterMode Then ' gettin an error "out of range" Set af = Worksheets("Initiatives").AutoFilter iFilterCount = 1 Worksheets("Initiatives").Unprotect ("donit") For Each fFilter In af.Filters If fFilter.On Then af.Range.Cells(1, iFilterCount) _ .Interior.ColorIndex = 3 ' 15 - gray; 35 - mint green Else af.Range.Cells(1, iFilterCount) _ .Interior.ColorIndex = 15 End If iFilterCount = iFilterCount + 1 Next fFilter Else Worksheets("Initiatives").Range("A1").AutoFilter Worksheets("Initiatives").Range("A1:H1").Interior. ColorIndex = 15 End If How could I fix it? Thanks |
All times are GMT +1. The time now is 10:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com