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.
--
Gary's Student


"MSPLearner" wrote:

> 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

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


"MSPLearner" <(E-Mail Removed)> wrote in message
news:4D29F515-206F-4D2A-8A10-(E-Mail Removed)...
> 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

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

Regards,
Peter T

"Peter T" <peter_t@discussions> wrote in message
news:uqDw#(E-Mail Removed)...
> 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
>
>
> "MSPLearner" <(E-Mail Removed)> wrote in message
> news:4D29F515-206F-4D2A-8A10-(E-Mail Removed)...
> > 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
>
>