How do I count rows highlighted with color fill?
Posted by Charlie on July 11, 2000 11:14 AM
I am working on a large project that contains over 20000 entries (rows), 14 columns. Each row has unique info pertaining a comm system program.
Now every time I check each one for status, I use color fill across the row to identify the action taken:
RED - Deactivated
GREEN - Active
LIME - Pending
YELLOW - Re-check status
No Fill - Not yet checked
I thought I could set up 5 independent cells with formulas to count the color filled cells as I went along, but I cannot figure out how to include the color fill as an argument within a formula.
Any ideas?
Any help is appreciated!! :)
Charlie
Posted by Ryan on July 11, 0100 12:38 PM
Charlie,
Here are 5 functions that will count the colored rows for you. If you put these in a code module for your workbook and then in a cell type:
"=CountRed()", it will return a value of rows that are red.
I did my best in assigning the color codes, but i'm not sure if they are the same codes that your workbook is, so you will need to check that, and make corrections if needed. Let me know if you need some more help!
Ryan
Function CountRed()
LastRow = Range("A65000").End(xlUp).Row
CountRed = 0
For x = 1 To LastRow
Debug.Print Rows(x).Interior.ColorIndex
If Rows(x).Interior.ColorIndex = 46 Then CountRed = CountRed + 1
Next x
End Function
Function CountYellow()
LastRow = Range("A65000").End(xlUp).Row
CountYellow = 0
For x = 1 To LastRow
Debug.Print Rows(x).Interior.ColorIndex
If Rows(x).Interior.ColorIndex = 6 Then CountYellow = CountYellow + 1
Next x
End Function
Function CountGreen()
LastRow = Range("A65000").End(xlUp).Row
CountGreen = 0
For x = 1 To LastRow
Debug.Print Rows(x).Interior.ColorIndex
If Rows(x).Interior.ColorIndex = 50 Then CountGreen = CountGreen + 1
Next x
End Function
Function CountLime()
LastRow = Range("A65000").End(xlUp).Row
CountLime = 0
For x = 1 To LastRow
Debug.Print Rows(x).Interior.ColorIndex
If Rows(x).Interior.ColorIndex = 43 Then CountLime = CountLime + 1
Next x
End Function
Function CountNoFill()
LastRow = Range("A65000").End(xlUp).Row
CountNoFill = 0
For x = 1 To LastRow
Debug.Print Rows(x).Interior.ColorIndex
If Rows(x).Interior.ColorIndex = xlNone Then CountNoFill = CountNoFill + 1
Next x
End Function
