sum colored cells

[Guest]

I have the following code in module1.
I`m trying to sum values in cells column D based on the color of cells in
column E that are yellow?

Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of the Font in the cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1, 1).Font.ColorIndex
Else
CellColorIndex = InRange(1, 1).Interior.ColorIndex
End If

End Function
Function SumIfByColor(InRange As Range, _
WhatColorIndex As Integer, SumRange As Range, _
Optional OfText As Boolean = False) As Variant
'
' This function will return the SUM of the values of cells in
' SumRange where the corresponding cell in InRange has a background
' color (or font color, if OfText is true) equal to WhatColorIndex.
'
Dim OK As Boolean
Dim Ndx As Long

Application.Volatile True

If (InRange.Rows.Count <> SumRange.Rows.Count) Or _
(InRange.Columns.Count <> SumRange.Columns.Count) Then
SumIfByColor = CVErr(xlErrRef)
Exit Function
End If

For Ndx = 1 To InRange.Cells.Count
If OfText = True Then
OK = (InRange.Cells(Ndx).Font.ColorIndex = WhatColorIndex)
Else
OK = (InRange.Cells(Ndx).Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(SumRange.Cells(Ndx).Value) Then
SumIfByColor = SumIfByColor + SumRange.Cells(Ndx).Value
End If
Next Ndx

End Function

nothing happens??

 

[Bernard Liengme]

Have a look at one or more of these
www.ozgrid.com/VBA/Sum.htm
www.cpearson.com/excel/colors.htm
www.xldynamic.com/source/xld.ColourCounter.html

or Google with term: excel sum by color ; to get other links
best wishes

 

[Gord Dibben]

Where are you entering the formula that uses these Functions and what is the
formula?

I assume some type of SUMIF formula entered in a cell as Chip illustrates

You can call this function from a worksheet cell with a formula like

=SUMIFBYCOLOR(A1:A10,3,B1:B10,FALSE)

Maybe go back to Chip's site and re-read the instructions.

http://www.cpearson.com/excel/colors.htm



Gord Dibben MS Excel MVP

 

[Peo Sjoblom]

So what do you put in the cell?


colorindex of yellow is 6



if you don't know how to install UDF or macros see

http://www.mvps.org/dmcritchie/excel/install.htm



--


Regards,


Peo Sjoblom

 

[Guest]

Cells C1 to C4 contain values of 1,2,3,7 and cells D1 to D4 are coloured
yellow (value=6)


In say E1 put:

=sumifbycolor(D14,6,C1:C4)

Result is 13

HTH