Hi wavemehello,
let me offer an alternative to the GETCOLOR based one (my message dated Thu Oct 12, 2006 10:54 am).
It is based on the concept that you doubleclick on a coloured cell and the all cells with a different color are hidden.
If you like this concept, do as follows:
1) open the vba editor (via Alt-F11)
2) in the Project area (the frame on the left) doubleclick on “ThisWorkbook”
3) on the (blank) frame at the right copy the following macro:
Code:
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
FirstCellToFilter = "C4" '<<< Change as necessary
'
ColourCol = Range(FirstCellToFilter).Column
Sheets("Sheet1").Cells.EntireRow.Hidden = False
If ActiveCell.Row = 1 Then GoTo Finish
'Application.ScreenUpdating = False
CurColor = ActiveCell.Interior.ColorIndex
LastRow = Cells(65536, ColourCol).End(xlUp).Row
Range(FirstCellToFilter).Select
For I = Range(FirstCellToFilter).Row To LastRow
Cells(I, ColourCol).Select
If Selection.Interior.ColorIndex <> CurColor Then Selection.EntireRow.Hidden = True
Next I
'
Application.ScreenUpdating = True
Finish:
Range("A1").Select
End Sub
Edit, if necessary, the instruction FirstCellToFilter = "C4"; here you specify which is the coloured column and which is the first row of valid data to filter; you may not specify row 1, that is intended to be a header and is used to restore all the lines, and you might allocate some rows for the legenda. For example, using C4 means that rows 1-2-3 will not be filtered on their color.
Use:
-doubleclick on a cell having the colour that you wish to select: the macro will be activated and all the rows having a different colour will be hidden.
-doubleclick on row 1 and all the lines will be shown
You can forget about the GETCOLOR function and you don’t need an extra column.
If you prefer to remain with the Getcolor solution, then please modify the code as follows:
Code:
Function Getcolor(Mycell As Range)
Application.Volatile
Getcolor = Mycell.Interior.ColorIndex
End Function
The added line will make the color-index updated at each recalculation of the sheet (ie any new data entered, or on F9).
Hope this helps. Bye,