I would like to build a formula that will evaluate the sum of a range of cells based on not only the category they relate to, but also on the colour that specific shells are shaded. I've attached an example spreadsheet of what I'm trying to do to help visualise this better.
Any help would be greatly appreciated.
heres UDF i came up with...its pretty specific to your example though
included the getbackground color UDF so you know where i get the random numbers from (which is colorindex)
Function SumColor(MyRange As Range, Color As Long, Cat As String) Dim cell As Range For Each cell In MyRange If Cells(cell.Row, 1).Value = Cat Then If cell.Interior.ColorIndex = Color Then SumColor = SumColor + cell.Value End If End If Next End Function
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Thanks humdingaling. I seem to be getting a 0 returned in the SumColor formula. Does the color index need to be a value, or can I simply concatenate the formulae together?
UDF
Use in cell B22
=SumCatCor($A22,$A$4:$M$18,B$21)
Fill right + down
Function SumCatCor(myCategory, rng As Range, myColor) As Double Dim r As Range, ColorNum As Long, i As Long Application.Volatile Select Case UCase$(myColor) Case "BLUE": ColorNum = 13998939 Case "GREEN": ColorNum = 4697456 Case "AMBER": ColorNum = 49407 Case "RED": ColorNum = 255 End Select For i = 1 To rng.Rows.Count If rng.Cells(i, 1).Value = myCategory Then For Each r In rng.Rows(i).Cells If r.Interior.Color = ColorNum Then SumCatCor = SumCatCor + Val(r.Value) Next End If Next End Function
Last edited by jindon; 10-14-2015 at 11:35 PM.
Thanks as well Jindon. I don't appear to have used the same colours in my original spreadsheet than the one I attached (my own fault). How do I obtain the different colour identifiers used in your UDF above?
You need to replace the Color Numbers
You could obtain color number:Select Case UCase$(myColor) Case "BLUE": ColorNum = 13998939 Case "GREEN": ColorNum = 4697456 Case "AMBER": ColorNum = 49407 Case "RED": ColorNum = 255 End Select
1 Select the Colored cell that you want to get Color Number
2 Open VBE(Visual Basic Editor) Alt + F11
3 GoTo [View] - [ImmediateWindow]
4 type
?ActiveCell.Interior.Color
Then hit Enter key
It will give you color number that you want.
But this is not a smart way.
I would prefer to get the color from cell in the function like this
In cell B22:
=SumCatCor($A22,$A$4:$M$18,B$21)
Where B21 (Header) has colored like attached.
Function SumCatCor(myCategory, rng As Range, ref As Range) As Double Dim r As Range, myColor As Long, i As Long Application.Volatile myColor = ref.Interior.Color For i = 1 To rng.Rows.Count If rng.Cells(i, 1).Value = myCategory Then For Each r In rng.Rows(i).Cells If r.Interior.Color = myColor Then SumCatCor = SumCatCor + Val(r.Value) Next End If Next End Function
yes colorindex needs to be a number
as per post #2, i included an additional UDF for you to get the colorindex
see row 30 of my sheet, i used getbackgroundcolor() UDF
the situation is similar to what Jindon Posts in post #6
however, Jindon's new code in post #6 has it built in as well as making the UDF react to changes within the sheet
i suggest you just take his version as it is a better version of what i had initially
That works a treat! Thanks for both of your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks