If function using cell format as the condition

[Janie]

I would like to create an "If" logical function formula, where the "true"
condition is based on the format of the cell.

For instance, if cell b3 is formatting with a fill color, then add that
number; if not, then add zero.

Can anyone help?

 

[Jacob Skaria]

There is no built-in excel function to do this. But you can use a UDF that
looks at the range and returns the sum of color. But that function will not
recalculate if you change color. Every time you change the color you will
need to recalculate or wait excel to recalculate...

To install the UDF (User Defined function). From workbook launch VBE using
Alt+F11. From menu Insert a Module and paste the below function.Close and get
back to workbook and try the below formula.

=colorsum(A5:A9,D5)
cell D5 will have the fill color which is the query color...to be searched
in the range A5:A9

'If you mean to sum the cell values with matching color then
Function ColorSum(varRange As Range, varColor As Range)
Dim arrTemp As Variant, varTemp As Variant
For Each cell In varRange
varTemp = cell.Interior.ColorIndex
If varTemp = varColor.Interior.ColorIndex Then ColorSum = ColorSum +
cell.Value
Next
End Function

 

[Jacob Skaria]

Use the below code instead....(the previous one might give you syntax issues
due to text alignment)

'If you mean to sum the cell values with matching color then
Function ColorSum(varRange As Range, varColor As Range)
Dim arrTemp As Variant, varTemp As Variant
For Each cell In varRange
varTemp = cell.Interior.ColorIndex
If varTemp = varColor.Interior.ColorIndex Then
ColorSum = ColorSum + cell.Value
End If
Next
End Function