doing average

[sna]

Hi Dear!
I have written a macro in form of custom function to average cells base on red color font.
I checked code right but why not calculate right?

Code: Select all

Function AverageRed(rng As Range)
  Dim C As Range
  Dim Count As Long
  Dim Result As Double

  For Each C In rng
    If C.Font.ColorIndex=3 And IsNumeric(C.Value) Then
        Result = Result + C.Value
        Count = Count + 1
    End If
    Next 

  If Count = 0 Then
    AverageRed = ""
  Else
    AverageRed = Result / Count
  End If
End Function

What's wrong ,any matter will share file
Best Wishes,

[snasui]

Please attach the example Excel file with code above inside.

[sna]

Here's an attached
the first calculation is right but when change font to black it is not working well


Best Wishes,

[snasui]

Your attached file is incorrect file type. The extension should be .xlsm instead .xlsx.

Changing the font color does not trigger your formula. Please see this link for more information. https://stackoverflow.com/questions/563 ... th-changes

[sna]

Ok thanks for the link
I will take a look

Best Wishes,

[sna]

File attached