Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Attached Files Attached Files
    Regards
    Prasad

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    That is Rory's Autofilter Highlighter Add-in.

    You could change the UpdateHilites procedure as follows:

    Code:
    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.

  3. #3
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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:

    Code:
    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??
    Regards
    Prasad

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [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.

  5. #5
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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?
    Regards
    Prasad

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  7. #7
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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??
    Regards
    Prasad

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •