In the example attached, I wanted to know if there was a formula that would count the colors of each cell per row.
Or if there was another way to note cells so that they may be counted.
Thanks for any help.
Nick
In the example attached, I wanted to know if there was a formula that would count the colors of each cell per row.
Or if there was another way to note cells so that they may be counted.
Thanks for any help.
Nick
Hi avidcat,
Unfortunately, Excel doesn't native function to do that. However, there some very useful UDFs available that can do this job very well
Please check out this link below. This UDF can be used to count colored cells and also do the summation.
http://www.ablebits.com/office-addin...nt-cells-color
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Use the following code..
but you sheet know the Colorindex of the color to be counted..
sub countColor() x=application.inputbox("enter the colorindex of the color you wanna count",type:=2) for each cell in Range("D4:L43") if cell.interior.colorindex=x then c=c+1 next msgbox c end sub
Don't forget to click *
Option Explicit Sub Macro1() Const Blue = 37 Const Yellow = 6 Const Purple = 47 Dim DayColumn As Variant, _ EmplRow As Long, _ EmplCount As Long, _ TestSheet As Worksheet Set TestSheet = ActiveSheet With TestSheet EmplCount = .Cells(Rows.Count, "A").End(xlUp).Row .Range("P4:R" & EmplCount).ClearContents For EmplRow = 4 To EmplCount For Each DayColumn In Array("D", "E", "F", "G", "H", "I", "J", "K") Select Case .Cells(EmplRow, DayColumn).Interior.ColorIndex Case Blue .Cells(EmplRow, "P").Value = .Cells(EmplRow, "P").Value + 1 Case Yellow .Cells(EmplRow, "Q").Value = .Cells(EmplRow, "Q").Value + 1 Case Purple .Cells(EmplRow, "R").Value = .Cells(EmplRow, "R").Value + 1 End Select 'colorindex test Next DayColumn Next EmplRow End With 'testsheet End Sub
Ben Van Johnson
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks