Highlight Filter (Addin)

[prasad]

Hi, I hve an addin to highlight filtered range in specified colour. So far single range is filtered, it is fine. But when I go for another filter, it highlight the 2nd range in same color. Is it possible to choose different colors for multi-filtered ranges? Pls help.

[HansV]

That is Rory's Autofilter Highlighter Add-in.

You could change the UpdateHilites procedure as follows:

Private Sub UpdateHilites()
  Dim rngCell As Range, fmcTemp As FormatCondition
  On Error Resume Next
  Dim n As Integer
  With ActiveSheet
	If Not .AutoFilter Is Nothing Then
	  n = 2
	  With .AutoFilter.Range
		For Each rngCell In .Rows(1).Cells
		  n = n + 1
		  rngCell.FormatConditions.Delete
		  Set fmcTemp = rngCell.FormatConditions.Add(xlExpression, , "=IsFilterOn")
		  With fmcTemp
			.Interior.ColorIndex = n
		  End With
		Next rngCell
	  End With
	End If
  End With
End Sub

but the result will be rather garish.

[prasad]

[quote name='HansV' post='782284' date='29-Jun-2009 12:36']That is Rory's Autofilter Highlighter Add-in.

You could change the UpdateHilites procedure as follows:

Private Sub UpdateHilites()
  Dim rngCell As Range, fmcTemp As FormatCondition
  On Error Resume Next
  Dim n As Integer
  With ActiveSheet
	If Not .AutoFilter Is Nothing Then
	  n = 2
	  With .AutoFilter.Range
		For Each rngCell In .Rows(1).Cells
		  n = n + 1
		  rngCell.FormatConditions.Delete
		  Set fmcTemp = rngCell.FormatConditions.Add(xlExpression, , "=IsFilterOn")
		  With fmcTemp
			.Interior.ColorIndex = n
		  End With
		Next rngCell
	  End With
	End If
  End With
End Sub

but the result will be rather garish.[/quote]

but i don't know how to edit/modify an addin??

[HansV]

[quote name='prasad' post='782287' date='29-Jun-2009 14:24']but i don't know how to edit/modify an addin?? [/quote]
Press Alt+F11 to activate the Visual Basic Editor.
Open the basHilite module by double-clicking it in the Project Explorer on the left hand side.
When done, press Ctrl+S to save the changes.
Press Alt+F11 to switch back to Excel.

[prasad]

[quote name='HansV' post='782288' date='29-Jun-2009 13:33']Press Alt+F11 to activate the Visual Basic Editor.
Open the basHilite module by double-clicking it in the Project Explorer on the left hand side.
When done, press Ctrl+S to save the changes.
Press Alt+F11 to switch back to Excel.[/quote]
I did it five times and left the editor without saving, assuming that it will save the changes automatically on close. Now i can count the steps of filter as it assign different color for every filter but the problem is to identify the sequence. Is there a way to identify which range is filtered first and so on?

[HansV]

That would be difficult because Excel doesn't have a trappable event that occurs when the user filters data. You'd have to store all information about the filter and use the Worksheet_Calculate event and compare the current filter with the stored info to see what has changed - not an attractive idea.

[prasad]

[quote name='HansV' post='782297' date='29-Jun-2009 14:25']That would be difficult because Excel doesn't have a trappable event that occurs when the user filters data. You'd have to store all information about the filter and use the Worksheet_Calculate event and compare the current filter with the stored info to see what has changed - not an attractive idea.[/quote]
and how it picks the colors? on randon basis??

[HansV]

The code that I posted uses colorindex = 3 for the first column of the autofiltered range, colorindex = 4 for the second column, etc.
The default color palette for Excel 97 - 2003 has 1 = black, 2 = white, 3 = red, 4 = green, 5 = blue, 6 = yellow, 7 = magenta, 8 = cyan etc.
(The code will fail if the autofiltered range has more than 54 columns)