Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-22-2016, 06:29 PM
zhead zhead is offline How to Add a line of code to count highlighted cells Windows 7 64bit How to Add a line of code to count highlighted cells Office 2010 64bit
Advanced Beginner
How to Add a line of code to count highlighted cells
 
Join Date: Mar 2015
Location: Texas
Posts: 32
zhead is on a distinguished road
Question How to Add a line of code to count highlighted cells

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
Reply With Quote
  #2  
Old 06-23-2016, 04:43 AM
Debaser's Avatar
Debaser Debaser is offline How to Add a line of code to count highlighted cells Windows 7 64bit How to Add a line of code to count highlighted cells Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Location: UK
Posts: 221
Debaser will become famous soon enough
Default

You need:

Code:
Selection.AutoFilter Field:=12, Criteria1:=RGB(255, 255, 0), Operator:=xlFilterCellColor
Reply With Quote
  #3  
Old 06-23-2016, 05:06 AM
zhead zhead is offline How to Add a line of code to count highlighted cells Windows 7 64bit How to Add a line of code to count highlighted cells Office 2010 64bit
Advanced Beginner
How to Add a line of code to count highlighted cells
 
Join Date: Mar 2015
Location: Texas
Posts: 32
zhead is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 06-23-2016, 05:28 AM
Debaser's Avatar
Debaser Debaser is offline How to Add a line of code to count highlighted cells Windows 7 64bit How to Add a line of code to count highlighted cells Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Location: UK
Posts: 221
Debaser will become famous soon enough
Default

No - COUNTIFS won't count colours.

In an ideal world you would use colours to reflect your data, not as data.
Reply With Quote
  #5  
Old 06-23-2016, 05:31 AM
zhead zhead is offline How to Add a line of code to count highlighted cells Windows 7 64bit How to Add a line of code to count highlighted cells Office 2010 64bit
Advanced Beginner
How to Add a line of code to count highlighted cells
 
Join Date: Mar 2015
Location: Texas
Posts: 32
zhead is on a distinguished road
Default

Ok Thanks. That is unfortunate.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Add a line of code to count highlighted cells 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
How to Add a line of code to count highlighted cells 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

Other Forums: Access Forums

All times are GMT -7. The time now is 02:42 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft