Anybody know how to do it?
Direct formula? VBA?
Direct formula? VBA?
Post Reply
1 of 1
Excel Help - Counting Cells of a Certain Color
481 Views |
5 Replies |
Last: 9 yr ago by denied
Custom function built in VBA. If you can wait until tomorrow afternoon I can post the function. It's on my laptop at the office.
Go to chandoo.org and search. It's the holy grail of excel help.
I am patiently waiting. The sooner the better though.
And I may (do) need instructions on how to clear out all of the modules and put in the code. I haven't used VBA since Excel 2003 in 2004.
[This message has been edited by denied (edited 8/28/2014 9:06a).]
And I may (do) need instructions on how to clear out all of the modules and put in the code. I haven't used VBA since Excel 2003 in 2004.
[This message has been edited by denied (edited 8/28/2014 9:06a).]
edit: this is more confusing than helping. Wait for the guy with the function.
[This message has been edited by diehard03 (edited 8/28/2014 10:26a).]
[This message has been edited by diehard03 (edited 8/28/2014 10:26a).]
This is what I came up with when I needed it:
Then, I used the function in a cell like this:
=ColorFunction(Cell Reference to a cell with the color you want to count, range, false)
This will not calculate automatically. You either have to go to the cell and press Ctrl+Alt+F9 or click Formulas --> Calculate Now
quote:
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
'Sums or counts cells based on a specified fill color.
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function
Then, I used the function in a cell like this:
=ColorFunction(Cell Reference to a cell with the color you want to count, range, false)
This will not calculate automatically. You either have to go to the cell and press Ctrl+Alt+F9 or click Formulas --> Calculate Now
I appreciate the help. I actually got it working about an hour before you posted it. But you validated what we did, including the need to refresh macros.
AgDad121619
Report: Texas A&M Has Contacted Indiana RB Trent Howland
in Billy Liucci's TexAgs Premium
53
SoTx Ag
Report: Texas A&M Has Contacted Indiana RB Trent Howland
in Billy Liucci's TexAgs Premium
49
1/2 Man 1/2 Amazing
Report: Texas A&M Has Contacted Indiana RB Trent Howland
in Billy Liucci's TexAgs Premium
42
Good Poster
Report: Texas A&M In Contact With Penn St WR Keandre Lambert-Smith
in Billy Liucci's TexAgs Premium
31
The Vonster
2025 4-Star WR Adrian Wilson Trending To Texas A&M Per Chad Simmons
in Billy Liucci's TexAgs Premium
31