You are here:

Excel/sum of same font values

Advertisement


Question
QUESTION: I tried the following code
Function SumByGreenFont(InRange As Range)
  Dim Rng As Range
  Application.Volatile True
  For Each Rng In InRange.Cells
      If Rng.Font.ColorIndex = 10 And IsNumeric(Rng.Value) Then
          SumByGreenFont = SumByGreenFont + Rng.Value
      End If
  Next Rng
End Function

However it comes up #value! and macro indicates syntex error. can you help ?
Regards Chris

ANSWER: Chris,

the code worked fine for me.  Did you put it in a general module in the same workbook where you are trying to use it.  Make sure you only have one workbook open.  go to the Visual Basic Editor and do Insert =>Module in the menu.  Place the code in that module.  Don't put it in a sheet module or any other type of class module.  

Usage:
in a cell,
=SumByGreenFont(A1:A100)

Since it worked fine for me, there are no syntax or coding errors.

--
Regards,
Tom Ogilvy
 



---------- FOLLOW-UP ----------

QUESTION: That worked, thank you for you assistance.
How do i put code in for 4 different colors relative to the same cell range ?

ANSWER: Chris,

... correction on the showcolors() routine ...  see below

Depends on what you mean by 4 different colors. If you mean you want to sum if it is any of 4 colors then you would use an or statement.  

Assume the colorindex numbers are   3, 4, 5 or 10

Function SumByGreenFont(InRange As Range)
 Dim Rng As Range
 Application.Volatile True
 For Each Rng In InRange.Cells
     If (Rng.Font.ColorIndex = 10 or _
        Rng.Font.ColorIndex = 3 or _
        Rng.Font.ColorIndex = 4 or _
        Rng.Font.ColorIndex = 5)  And IsNumeric(Rng.Value) Then
         SumByGreenFont = SumByGreenFont + Rng.Value
     End If
 Next Rng
End Function


if want to change the colorindex and have a 4 separate functions, just copy and rename the functions and change the colorIndex number

If you want to see what the numbers are you can run this on a blank worksheet

[ had a type.  the Cells(1,...   should be Cells(I,...  in both lines]
Sub showcolors()
for I = 1 to 56
 Cells(i,"A").value = I
 cells(i,"B").Interior.ColorIndex = I
Next
End Sub

If that those don't answer your question, then explain what you want to do.

--
Regards,
Tom Ogilvy



---------- FOLLOW-UP ----------

QUESTION: Thank you for your prompt response. I want to run 4 separate functions for individual results in separate cells.Can I do 4 separate modules on the same workbook
Regards Chris

Answer
Chris,

In a single module you could have 4 separate functions which have different names.  For example:

Function SumByGreenFont(InRange As Range)
 Dim Rng As Range
 Application.Volatile True
 For Each Rng In InRange.Cells
     If Rng.Font.ColorIndex = 10 And IsNumeric(Rng.Value) Then
         SumByGreenFont = SumByGreenFont + Rng.Value
     End If
 Next Rng
End Function


Function SumByBlueFont(InRange As Range)
 Dim Rng As Range
 Application.Volatile True
 For Each Rng In InRange.Cells
     If Rng.Font.ColorIndex = 5 And IsNumeric(Rng.Value) Then
         SumByBlueFont = SumByBlueFont + Rng.Value
     End If
 Next Rng
End Function

Function SumByRedFont(InRange As Range)
 Dim Rng As Range
 Application.Volatile True
 For Each Rng In InRange.Cells
     If Rng.Font.ColorIndex = 3 And IsNumeric(Rng.Value) Then
         SumByRedFont = SumByRedFont + Rng.Value
     End If
 Next Rng
End Function


Function SumByYellowFont(InRange As Range)
 Dim Rng As Range
 Application.Volatile True
 For Each Rng In InRange.Cells
     If Rng.Font.ColorIndex = 6 And IsNumeric(Rng.Value) Then
         SumByYellowFont = SumByYellowFont + Rng.Value
     End If
 Next Rng
End Function

These could all be place in the module you are currently using for the Green version of the function.  


You could also make a singe function more general:

Function SumByFontColor(InRange As Range, idex as long)
 Dim Rng As Range
 Application.Volatile True
 For Each Rng In InRange.Cells
     If Rng.Font.ColorIndex = idex And IsNumeric(Rng.Value) Then
         SumByFontColor = SumByFontColor + Rng.Value
     End If
 Next Rng
End Function

usage:

=SumByFontColor(A1:A10,10)    to sum by green

=SumByFontColor(A1:A10,5)    to sum by blue

or
=SumByFontColor(A1:A10,B1)  were B1 holds the colorindex number you want to sum by

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.