Filter highlighter

[HansV]

That's wonderful, Rory! Thank you very much, it looks great!

[dasadler]

How can I configure Excel 2007 such that this filter highlighter add-in is ALWAYS present and available? Even when starting new workbooks & sheets.

[HansV]

Install it as an add-in:

Save the .xla or .xlam to a convenient folder, for example your Addins folder (C:\Users\<username>\AppData\Roaming\Microsoft\AddIns under Window Vista and Windows 7, or C:\Documents and Settings\<username>\Application Data\Microsoft\AddIns under Windows XP). But any folder will do.

In Excel 2007, click the Office button, then Excel options.
Click Add-Ins in the navigation pane on the left hand side.
Select Excel Add-ins from the Manage: dropdown at the bottom of the dialog (this is the default choice), then click Go...
If you see Filter Highlighter in the list of available add-ins, tick its check box.
Otherwise, click Browse..., navigate to the .xla / .xlam, select it and click OK. The add-in should now appear in the list; tick its check box.
Click OK.
The add-in should now be permanently available.

[dasadler]

Great - thank you very much.

[gailb]

I just downloaded this and get an error at the line

Code: Select all

Private Declare Function ChooseColor Lib "comdlg32.dll" Alias "ChooseColorA" (pColorStruct As ColorStruct) As Long

Is this due to I have a 64bit system?

[HansV]

Do you have the 64-bit version of Office?

[gailb]

Yes

[HansV]

Change the line

Code: Select all

Private Declare Function ChooseColor Lib "comdlg32.dll" Alias "ChooseColorA" (pColorStruct As ColorStruct) As Long

to

Code: Select all

Private Declare PtrSafe Function ChooseColor Lib "comdlg32.dll" Alias "ChooseColorA" (pColorStruct As ColorStruct) As LongPtr

and the lines

Code: Select all

Private Type ColorStruct
  lStructSize As Long
  hwndOwner As Long
  hInstance As Long
  rgbResult As Long
  lpCustColors As String
  flags As Long
  lCustData As Long
  lpfnHook As Long
  lpTemplateName As String
End Type

to

Code: Select all

Private Type ColorStruct
  lStructSize As LongPtr
  hwndOwner As LongPtr
  hInstance As LongPtr
  rgbResult As LongPtr
  lpCustColors As String
  flags As LongPtr
  lCustData As LongPtr
  lpfnHook As LongPtr
  lpTemplateName As String
End Type

[gailb]

Thanks Hans, works great. Also, thanks to rory.

[jolas]

Does this work with 64bit version of Excel 2013?

[HansV]

Yes, with the code changes in my previous reply.

[jolas]

On Excel 2013 64 bit I am getting the following compile error when clicking either choose highlight color or font.The filter heading will have a black cell color as well as black text as I cannot see the cell text. Could you help fix this error.
Thanks.

[HansV]

Does it work if you change

Function ShowColor(...) As Long

to

Function ShowColor(...) As LongPtr

[jolas]

The compile error moved to this area of the code

[HansV]

Change

Code: Select all

    Dim lngColour As Long

to

Code: Select all

    Dim lngColour As LongPtr

[jolas]

compileError2.jpg

[HansV]

Does this version work? (By the way, I wonder why it worked for gailb and not for you...)

Hilite Filters.zip

[jolas]

compileError3.jpg

[HansV]

And now?

Hilite Filters.zip

[jolas]

It is working now. Your commitment to help is amazing. Thank you for your expertise and patience.