Automate creation of event macro

simmerer

New Member
Joined
Sep 6, 2017
Messages
33
Hi,
So I have a monthly report that I have automated to refresh with new data and perform other preparations. One tool I have will cause the header to change color if filtered. That way I don't forget the filter and allow it to mess up the rest of my work. I want to add the creation of this macro on a sheet object to my other preparations which I run as a batch. Here is the code that I use:
Code:
Option Explicit

Private Sub Worksheet_Calculate()
  Dim c As Long
  Dim bFiltered As Boolean

  If ActiveSheet.AutoFilterMode Then
    With ActiveSheet.AutoFilter.Filters
      For c = 1 To .Count
        If .Item(c).On Then
          bFiltered = True
          Exit For
        End If
      Next c
    End With
    ActiveSheet.AutoFilter.Range.Rows(1).Interior.Color = IIf(bFiltered, vbRed, RGB(217, 217, 217))
  End If
End Sub

I was unsuccessful with recording this.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Forum statistics

Threads
1,148,290
Messages
5,745,879
Members
423,983
Latest member
blackworx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top