Check Font or Font color and take action
 

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

Check Font or Font color and take action
 

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)*C 1+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

Check Font or Font color and take action
 

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" wrote in message
...
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

Check Font or Font color and take action
 

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
...
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" wrote in message
...
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