Check Font or Font color and take action

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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
 
Forgot to add the UDF will not recalc if user changes font, force with
Ctrl-Alt-F9

Regards,
Peter T
 
Back
Top