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 SalvucciInformation Technology ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
[ 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 :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.