Check Font or Font color and take action

[Guest]

This might be off the wall, but I will ask anyway.

I have 4 cells A1 thru D1 with numbers 1,2,3,4 respectively. E1 = sum of A1
thru D1 = 10

Is there a way to examine the font style or font color of each of the 4
cells to determine if that cell's value can be part of the E1 sum? So if B2
font color = red, then it would not be part of sum E1, therefore E1 = sum of
A1,C1,D1 = 8.

Appreciate any help

 

[Guest]

First enter this UDF

Function is_it_red(r As Range) As Boolean
If r.Font.ColorIndex = 3 Then
is_it_red = True
Else
is_it_red = False
End If
End Function

and then in E1 enter:

=is_it_red(A1)*A1+is_it_red(B1)*B1+is_it_red(C1)*C1+is_it_red(D1)*D1

The function must be manually re-calculated if you change font color.

 

[Peter T]

Function SumNonRedFont(ref As Range) As Double
Dim ndSum As Double
Dim cel As Range

On Error Resume Next

For Each cel In ref
If cel.Font.Color <> vbRed Then
ndSum = ndSum + cel.Value
End If
Next
SumNonRedFont = ndSum

End Function

This does not cater for font coloured by NumberFormat or Conditional Format

Regards,
Peter T

 

[Peter T]

Forgot to add the UDF will not recalc if user changes font, force with
Ctrl-Alt-F9

Regards,
Peter T