Sum a row of data based on FONT COLOR (

R

RockNRoll

Alright, here's one for you. Sort of a wacky "SUMIF" function needed. Can
I conditionally sum a row of data in a worksheet if the font color is black?
Sample data


Column A Column B Column C Column D Column E Column F
(Normal Sum) Column G (Conditional color Sum)
1000(Black) 2000 (Red) 2000 (Black) 4000(Black) 1000(Green) 10,000
(1000+2000+4000)=7,000



Thanks for your help,
-Dave
 
M

Michelle

The following function will return the sum of cells in a
range that have either an Interior (background) or Font of
a specified color. InRange is the range of cells to
examine, WhatColorIndex is the ColorIndex value to count,
and OfText indicates whether to return the ColorIndex of
the Font (if True) or the Interior (if False).

Function SumByColor(InRange As Range, WhatColorIndex As
Integer, _
Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
Dim Rng As Range
Dim OK As Boolean

Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
OK = (Rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (Rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(Rng.Value) Then
SumByColor = SumByColor + Rng.Value
End If
Next Rng

End Function

You can call this function from a worksheet cell with a
formula like
=SUMBYCOLOR(A1:A10,3,FALSE)
 
Top