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

 

[jindon]

It should go to UserForm module, not in a Standard module.

right click on the form and select [viewcode] then paste there,

note: you need to change the commandbutton names to actual names...

 

[wavemehello]

I did as you said, right clicked the form and view code and pasted but came across this error message at,
Me.myfilter 3
"Method or data member not found"

command button names are default names so not need to rename.

 

[jindon]

Ok try to change

Me.myFilter 3 and Me.myFilter 4 to

myFilter 3 and myFilter 4 respectively

 

[jindon]

Ok try change to

Sub myFilter(n As Integer)
Dim r As Range
Application.ScreenUpdating = False
With Sheets("Sheet1")
   .Cells.EntireRow.Hidden = False
   With .Range("c1",.Range("c" & Rows.Count).End(xlUp))
      For Each r In .SpecialCells(xlCellTypeVisible)
         If r.Intrior.ColorIndex = n Then r.EntireRow.Hidden = True
      Next
   End With
End With
Application.ScreenUpdating = True
End Sub

 

[wavemehello]

-changed-compilation ok but run time error as "object does not support his property or method"
debug clicked and yellow highlight at code line - If r.Intrior.ColorIndex = n Then

 

[Anthony47]

Hi wavemehello,
I would do as follows:
1) put the following code in Module1 of your vba editor

Function Getcolor (Mycell As Range)
GetColor = Mycell.Interior.ColorIndex
End Function

2) on the first row of your data, put the following formula in a free column:
=GETCOLOR(A2)
(replace A2 with the coloured cell)
3) Copy this formula down for all the rows with useful data

With this you will have a column whose value represents the color of the referenced cell; apply to this color the standard filter method (Data ->Filter ->Automatic Filter).

Does this help?
Bye,

 

[wavemehello]

Hi Anthony!

That works fine and logical, but since there could be several colors later on and it would be hard to remember each color code, it would be great if I could make it automatic at the click of each color button in a form. Any better suggestions from anyone??

 

[Anthony47]

Hi wavemehello
I understand your point, but your concern should be on how colouring the cells, not on how filtering later on.

My practical suggestion is the following:
-for any of the used colour, add a "legenda" line (coloured) on top of your list, to remember the meaning of that colour.
-use Menu ->Windows ->freeze panes to have the legenda lines always visible on top of your window
-apply my formula also to these lines

With this technique:
-when inserting a colour, you can easily read which colour means what
-when you apply filters you can read in the filter drop-down list all the available colour indexes (not depending on the excel version) and you can immediately read the meaning of each index.

Anyway, a smarter approach is always better...

Bye,

 

[wavemehello]

Anthony!

Yes giving legends at the top and freezing the row panning is also an option, thanks for the suggestion but I was wondering of an interactive form with command buttons for the user. Thank you any way! bye.

 

[jindon]

-changed-compilation ok but run time error as "object does not support his property or method"
debug clicked and yellow highlight at code line - If r.Intrior.ColorIndex = n Then

Sorry typo

If r.InteriorColorIndex = n Then