filtering records based on fill colors possible?

[wavemehello]

hi there,
my worksheet contains staffnames and training completed/not completed which are indicated by different fill colors. If required, can I filter records based on the fill colors? If yes, how?

Thank you
Aj

 

[Anthony47]

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:

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:

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,

 

[wavemehello]

Hi anthony,

Thankx for the code but it does not seem to be working, I did exactly as you said. When I double click a coloured cell (fill color) nothing happens.

-To remind you, I have a workhseet with 3 columns. A,B and C.
Column A has names below with heading NAME (A1)
Column B has coursename below with heading COURSE(B1)
Column C has Remark below with heading STATUS (C1) and this is
where all the cells below are in red or green color.
-I changed the C4 in your code to C2 as my first record to search is in C2.
-I pasted the whole code in the THISWORKBOOK as well

-Any clue what went wrong?

 

[Anthony47]

Hi wavemehello,
you say that "nothing happens"...
Please help with the following information:
-do the data "flick" for a while after your double clic?
-if you doubleclick on, for example, C3 which is the selected cell at the end?
-are we talking about cell background color (as I understood) or something else?
-has the color been applied by direct cell formatting (Format ->Cell) or by Conditional Format? To answer this second part of the question, please select a colored cell, then select Format ->Conditional format and look in the conditional format windows if there is any formatting condition.
-finally, which excel version are you using? (via Help or ? ->Information on Microsoft excel)

Let us know, bye.

 

[Anthony47]

Hi,
pardon me for this addictional questions:
-if you opne the vba editor and doubleclick, in the Project area, on Sheet1 is anything shown on the code area (the frame on the right)?
-and what if, after that, doubleclick on ThisWorkbook?

Bye,

 

[wavemehello]

Hi Anthony,

Really sorry for troubling you. Started feeling little bit ashamed of myself here are you answers:
-There is not visible flicking
-Selected cell at the end is A1
-Cell background color (via toolbar)
-No conditional formatting
-Using excel 2003
-Nothing in Code area of sheet 1
-you code is in Thisworkbook

Thank you for your kind concern.

 

[Anthony47]

Well, your info should prove that the macro runs. Nevertheless ....

Please replace your existing macro under ThisWorkbook with the following:

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 = 3 Then RedCnt = RedCnt + 1
If Selection.Interior.ColorIndex = 4 Then GreenCnt = GreenCnt + 1
If Selection.Interior.ColorIndex <> CurColor Then
Selection.EntireRow.Hidden = True
FiltCnt = FiltCnt + 1
End If
Next I
'
Application.ScreenUpdating = True
Finish:
MsgBox ("Red= " & RedCnt & " -Green= " & GreenCnt & " -Filter: " & FiltCnt & " -Begin: " & FirstCellToFilter & " -Total Rows= " & LastRow)

Range("A1").Select
End Sub

At the end of the macro you should receive a message with 3 counters: please post this message when cliclink on line 1, on a green coloured cell, and on a red coloured cell.
When you have done, if you add a "single quotation mark" in front of the instruction MsgBox you will leave the macro enabled without the debug message.

An addictional question: did you try the GETCOLOR solution? if YES (or if you can try now) which is the colour code that you received? Where the color codes consistent on each of the lines?
And also: which version of excel do you use and which is its national language?

See you soon ....on the forum.

 

[Anthony47]

ERRATA: you should receive a message with 3 counters

CORRIGE: you should receive a message with 5 counters

Bye,

 

[tafigueroa]

If you want to Delete rows rather than Hide Rows

This may be been posted already, but I changed the code to delete the rows rather than hide them. Just some extra flavor to give you more flexiblity in what you want to do.


Sub test()
Dim i As Long
For i = Range("a" & Rows.Count).End(xlUp).Row To 2 Step -1
If Cells(i, "a").Interior.Color <> vbYellow Then
Rows(i).EntireRow.Delete
End If
Next
End Sub

 

[Anthony47]

It is a pity that this 47 answers topic remain in an unknown state...

Bye,