SUM numbers of a certain font color

[gudencough]

I am making a sheet of customers that have contracts to build objects. In
column A, there is a list of prices that are paid and not paid. The paid
items are formated in red font color. Column B is the amount we have paid
them.
Is there a way to formate colum B so that when i change a number to red in
column A, it will automatically update with the new SUM in colmn B?

Hopefully not too confusing.

 

[L. Howard Kittle]

Give this a try. Where there is a named range you want to count the red
fonts is named DataY

Sub SumColorCountRed()
Dim Red3 As Integer
Dim Cell As Range

For Each Cell In Range("DataY")
If Cell.Font.ColorIndex = 3 Then
Red3 = Red3 + Cell.Value
End If
Next

Range("F1").Value = "Red = " & Red3

MsgBox " Red adds to " & Red3, _
vbOKOnly, "CountColor"

Range("F1").Value = ""

End Sub

HTH
Regards,
Howard

 

[Chip Pearson]

You can do it with VBA. Put the following code in a module in your
workbook:


Function SumColor(RR As Range, _
ColorIndex As Long, _
Optional OfText As Boolean = False) As Double
Dim R As Range
Dim D As Double
For Each R In RR.Cells
If OfText = True Then
If R.Font.ColorIndex = ColorIndex Then
If IsNumeric(R.Value) Then
D = D + R.Value
End If
End If
Else
If R.Interior.ColorIndex = ColorIndex Then
If IsNumeric(R.Value) Then
D = D + R.Value
End If
End If
End If
Next R
SumColor = D
End Function

Then, you can call it from a worksheet cell with a formula like

=SumColor(A1:A100,3,TRUE)

The first parameter is the range of cells to test and sum. The second
argument is the ColorIndex of the cell that should be summed. In this
example, 3 indicates red. The third parameter should be TRUE if you
want to test font color or FALSE if you want to test background fill
color.

See www.cpearson.com/Excel/Colors.aspx for much more information about
working with colors in Excel.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[L. Howard Kittle]

Hi Chip,

I find that pretty darn slick. And playing with it, I see you can use a
cell reference for the True/False to toggle from font color to cell color,
say with a two item drop down list.

Goes in my archives.

Regards,
Howard

 

[gudencough]

This works great! Thank you.
Now i am wondering if there is a way for column B to automatically update
with a new sum when you format another number to red in coumn A?
Right now if you format a new number to red, you have to double click in
column B and press enter to update it. I'm wanting this for ease, and if
another user uses the spreadsheat in the future, there will be no confusion
if they forget to update column B.