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

 

[agihcam]

another way is to click on tools > macro > record macros. then select a cell and fill the color Red and on the other cell fill the color green then stop the recording using the blue square button, or tools macro stop recording. open the recorded macro codes from new module ( maybe module2) and post here.

 

[wavemehello]

yes, red is always fail, green is always pass but the question is how to use the color values for the condition? what will be the code?

 

[agihcam]

try recording the macro as suggested to obtain codes

 

[wavemehello]

I tried recording macro for color and the code are like below
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Range("F7").Select
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With

What I need is, I have a form userform1 that has 2 command buttons FAILED and PASSED. when I click the FAILED button, all the records having RED color fill should be displayed and when I click the PASSED command button, all the records having GREEN color fill should be displayed. So I put the code you gave me in the RED command button of USERFORM1 and called the from THISWORKBOOK_OPEN() using USERFOMR1.show, but nothing happened.

 

[agihcam]

what is the sheetname where your data is

 

[wavemehello]

sheet1

 

[agihcam]

ok paste this code in passed button;

private .......'your sub name
Dim i As Long
Sheets("Sheet1").Cells.Rows.Hidden = False
For i = Sheets("Sheet1").Range("c" & Rows.Count).End(xlUp).Row To 2 Step -1
    If Sheets("Sheet1").Cells(i, "c").Interior.ColorIndex <> 4 Then
        Rows(i).EntireRow.Hidden = True
    End If
Next
end sub

this code for failed button

private ..... 'your sub name
Dim i As Long
Sheets("Sheet1").Cells.Rows.Hidden = False
For i = Sheets("Sheet1").Range("c" & Rows.Count).End(xlUp).Row To 2 Step -1
    If Sheets("Sheet1").Cells(i, "c").Interior.ColorIndex <> 3 Then
        Rows(i).EntireRow.Hidden = True
    End If
Next
end sub

 

[jindon]

To UserForm module

Private Sub CommandButton1_Click()
Me.myFilter 3
End Sub

Private Sub CommandButton2_Click()
Me.myFilter 4
End Sub

Private Sub myFilter(n As Integer)
Dim r As Range, txt As String
Application.ScreenUpdating = False
With Sheets("Sheet1")
   .Cells.EntireRow.Hidden = False
Again:
   With .Range("c1",.Range("c" & Rows.Count).End(xlUp))
      For Each r In .SpecialCells(xlCellTypeVisible)
         If r.Intrior.ColorIndex = n Then
            txt = txt & r.Address(0,0) & ","
            If Len(txt) > 245 Then
               Sheets("Sheet1").Range(Left(txt,Len(txt)-1)).EntireRow.Hidden = True
               txt = Empty
               GoTo Again
            End If
         End If
      Next
   End With
   If Len(txt) Then .Range(Left(txt,Len(txt)-1)).EntireRow.Hidden = True
End With
Application.ScreenUpdating = True
End Sub

 

[wavemehello]

code blocks pasted in 2 command buttons, the fill colors are also in C column, your code also seems to have no mistakes, but what is happending at my end I dont understand coz, there is no change when I click the passed or failed button. What do u think can have gone wrong here?

 

[wavemehello]

Kombanwa Jindon,

you said the code goes in userform module. do I right click the userform and insert -module and paste the code? If i do that it gives error "invalid use of me keyword" when I compile? Can you elaborate please?

Arigatto