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

=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