I slightly change your code & it work great is it right???
Function SumColour(r As Range, iCol As String) As Long
Select Case UCase(iCol)
Case Is = "YELLOW"
cindex = 6
Case Is = "BLUE"
cindex = 5
Case Is = "GREEN"
cindex = 4
Case Is = "RED"
cindex = 3
Case Is = "BROWN"
cindex = 53
Case Else
End Select
For Each c In r
If c.Font.ColorIndex = cindex And _
IsNumeric(c) Then
SumColour = SumColour + c.Value
End If
Next c
End Function

--
--
Regards,



"Mike H" wrote:

Hi,

Try this instead. call with

=SumColour(A1:A15,"Yellow") or Red etc

Function SumColour(r As Range, iCol As String) As Long
Select Case UCase(iCol)
Case Is = "YELLOW"
cindex = 6
Case Is = "BLUE"
cindex = 5
Case Is = "GREEN"
cindex = 4
Case Is = "RED"
cindex = 3
Case Is = "BROWN"
cindex = 53
Case Else
End Select
For Each c In r
If c.Interior.ColorIndex = cindex And _
IsNumeric(c) Then
SumColour = SumColour + c.Value
End If
Next c
End Function

Mike

"Abdul Shakeel" wrote:

thanks for your reply but it is more difficult to call this formula with
colour number as I use several colours in my sheet could I use the colour
name instead

--
Regards,



"Mike H" wrote:

Hi,

You need a UDF for that. Try this one but note it doesn't work for
conditional format colours, that requires a different and much more
complicated method. Alt + F11 to open VB editor. Right click 'This workbook'
and insert module and paste ths code below in on the right.

Call with

=SumColour(A1:A20,6)
6 is yellow and if your not sure of the colours record a macro of yourself
colouring the cells to get the numbers.

Function SumColour(r As Range, iCol As Integer) As Long
For Each c In r
If c.Interior.ColorIndex = iCol And _
IsNumeric(c) Then
SumColour = SumColour + c.Value
End If
Next c
End Function

Mike

"Abdul Shakeel" wrote:

I wan to sum up values according to its font color....any idea??