"SUM" based on cell colour 1

[harrisharris]

Is it possible to use the SUM function to obtain results based on the colour pattern of the cells. For example, I have a column of numbers, the cells have various colour patterns. I need to be able to SUM some of the numbers by a particular colour only and some of the numbers containing mixed colours.

Thanks in anticipation.

 

[SteveBausch]

Let's start by defining the desired behavior.

Something like:

Click a button.

Userform with Color picker is displayed, asks user to pick a color.
Range picker dialog appears, asking user to choose the column/range/row.
User clicks a "Sum The Colors" on userform, action is performed.
The result is displayed where?

 

[xlbo]

This is particularly bad spreadsheet design. There is NO inbuilt way to count colours in excel - you would be far better off adding a text / numeric indicator to a cell in the same row as the data - this could then be used to do the conditional sums (which, in reality is what you are asking for). BUT - it can be done via a sub or UDF and being as it would probably be too time consuming to change this spreadsheet, here ias a UDF that will sum coloured cells but please bear this in mind for future reference. Colour is for viewing purposes only - you should really try and use hard data to categorise other data

Function sumcol(rng As Range, clr As String)
Dim ctrCol As Integer
Dim tempsum As Long
tempsum = 0
Select Case UCase(clr)
Case "RED"
ctrCol = 3
Case "YELLOW"
ctrCol = 6
Case "BLUE"
ctrCol = 5
Case "GREEN"
ctrCol = 4
Case "PURPLE"
ctrCol = 13
Case "PINK"
ctrCol = 7
Case "GREY"
ctrCol = 15
Case Else
sumcol = "Invalid Colour selected"
End Select

For Each c In rng
If c.Interior.ColorIndex = ctrCol Then
tempsum = tempsum + c.Value
Else
End If
Next
sumcol = tempsum
End Function

Please note that this will only work for those colours that I have entered - if you need to be able to use more colours, run this sub:

Sub listcols()
For i = 1 To 56
Range("A" & i).Interior.ColorIndex = i
Next i
End Sub

this will show all colours and the ROW number that they are on will refer to the would be what you assign to ctrcol in the function

To use the function, you would enter something like

=sumcol(A1:A100,"red&quot
which will sum all values in red in A1:A100

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[harrisharris]

Thanks Steve and Geoff,

I take the point that bad design is the case here. Your time is however appreciated.

I have decided not to dwell on this one anymore.