You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- sum of same font values

Advertisement

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

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

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | very prompt abs concise, an excellent service |

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

Answers by Expert:

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.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.