• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

Average values based on font color

I have a list in column C that depending on the account are either black font or red font. In column D I have the values I need to average. I need to be able to average the values in column D that have a red font in column C and then for the black font. Is this possible using vba?
Lawrence Salvucci
Lawrence Salvucci
  • 3
1 Solution
Steven HarrisPresidentCommented:
Do you have a sample worksheet?
[ fanpages ]IT Services ConsultantCommented:

Yes, it is possible.

Here is just one method...

Within the attached example workbook's code module, "basQ_28259046", I have added the following code:

Option Explicit
Public Function strFont_Color(ByRef rngCell As Range) As String

  Dim strReturn                                         As String
  On Error GoTo Err_strFont_Color
  Select Case (rngCell.Font.Color)
      Case (vbBlack)
          strReturn = "Black"
      Case (vbBlue)
          strReturn = "Blue"
      Case (vbRed)
          strReturn = "Red"
      Case (vbYellow)
          strReturn = "Yellow"
      Case Else
          strReturn = CStr(rngCell.Font.Color)
  End Select

  On Error Resume Next
  strFont_Color = strReturn
  Exit Function

  strReturn = "Error #" & CStr(Err.Number) & " - " & Err.Description
  Resume Exit_strFont_Color
End Function

Open in new window

There are ten rows of data within the attached workbook; some with an Account (in column [C]) in Red, & some in Black.  There is also one Account in Blue just to demonstrate that this row is not counted in the Total, Count, & Average calculations within the range [J1:M4].

Does such an approach meet your requirements?


[ fanpages ]IT Services ConsultantCommented:
...Another approach would be to remove the need of displaying the colo(u)r of the "Account" cell values ("Black", "Red", etc.) & write a Visual Basic for Applications function to calculate the average of a specified range of numeric values based on the colo(u)r of the associated "Account" (in the preceding column or, again, in a specified column upon the corresponding rows of the numeric values).

Hence, the additional column containing the colo(u)r text ("Black", "Red", etc.) would then not be required.

Please advise if this addresses your requirements to a greater degree.
Thank you.
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
That works just fine with the column calling out the font color. Thank you for your help!
[ fanpages ]IT Services ConsultantCommented:
You're very welcome :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now