Snasui Excel Forum

Posted: **Sun May 30, 2021 1:13 pm**

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,

Posted: **Sun May 30, 2021 1:30 pm**

Please attach the example Excel file with code above inside.

Posted: **Wed Jun 02, 2021 6:17 pm**

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

Best Wishes,

Posted: **Wed Jun 02, 2021 7:30 pm**

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

Posted: **Wed Jun 02, 2021 7:49 pm**

Ok thanks for the link
I will take a look

Best Wishes,

Posted: **Sat Jun 05, 2021 1:34 pm**

File attached

Snasui Excel Forum

doing average

doing average

Re: doing average

Re: doing average

Re: doing average

Re: doing average

Re: doing average