How to Add a line of code to count highlighted cells

zhead · #1 06-22-2016, 06:29 PM

I have this code that works great. But i am trying to add a line of code to count the highlighted cells. This is what i have. The line in reads "Selection.AutoFilter 12, "Yellow" is what i am trying to add with no success.

Code:

Sub Macro24()
'
' Macro24 Macro
'

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    Select Case ws.Name
        Case Is = "OC", "APARNA", "OD", "REPORT", "QUERY", "SC", "SUMMARY", "MA"
            GoTo zz
        Case Else
            ws.Activate
                Cells.Select
                Selection.AutoFilter 8, "OPEN"
                Selection.AutoFilter 6, "*SYS*"
                Selection.AutoFilter 12, "Yellow"
                Selection.AutoFilter 21, "OC"
                Selection.AutoFilter 3, "<>*MOC*"
                Selection.AutoFilter 16, "<>*MOC*"
                Range("A2:BC" & Range("A" & Rows.Count).End(3).Row).SpecialCells(12).Copy Sheets("OC").Range("A" & Rows.Count).End(3)(2)
                ActiveSheet.AutoFilterMode = False
    End Select
zz:
Next ws
End Sub

Debaser · #2 06-23-2016, 04:43 AM

You need:

Code:

Selection.AutoFilter Field:=12, Criteria1:=RGB(255, 255, 0), Operator:=xlFilterCellColor

zhead · #3 06-23-2016, 05:06 AM

Debaser
Thanks! that worked great.
Is it possible to add , Range("L:L"), "Yellow" to this?

Code:

With Range("AL2")
.Value2 = Application.CountIfs(Range("U:U"), "OC", Range("H:H"), "OPEN", Range("W:W"), ">0", Range("X:X"), ">15%", Range("F:F"), "*SYS GEN*", Range("C:C"), "<>*MOC*", Range("P:P"), "<>*MOC*")
.NumberFormat = "#,##0"
End With

Debaser · #4 06-23-2016, 05:28 AM

No - COUNTIFS won't count colours.

In an ideal world you would use colours to reflect your data, not as data.

zhead · #5 06-23-2016, 05:31 AM

Ok Thanks. That is unfortunate.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Formula cells are not highlighted when double clicking result	hockeytown	Excel	2	12-03-2015 09:57 AM
Multiple cells highlighted on mouse click	mrsm	Excel	0	03-02-2015 12:43 PM
White font not showing text when highlighted across multiple cells	hockeytown	Excel	3	12-16-2014 08:58 AM
predefine thick border colored lines for highlighted cells	dylansmith	Excel	0	09-05-2014 07:49 AM
Count range cells eliminating merge cells	danbenedek	Excel	0	06-15-2010 12:40 AM