1. CONDITIONAL

I need to know if there is a way to sum all the values WHERE the font is red?

THANKS

2. Re: CONDITIONAL

Is the font red based on a certain condition, such as negative value, date older than one year, part number greater than 9,000, or such?

3. Re: CONDITIONAL

A function to count based on font color was posted recently by Hans Pottel, and I am taking the liberty of posting an amended version to sum.
<pre>Function SumColors(R As Range, Col As Integer) As Integer
Application.Volatile True
Dim cell As Range
SumColors = 0
For Each cell In R
If cell.Font.ColorIndex = Col Then
SumColors = SumColors + cell.Value
End If
Next
End Function</pre>

Place that function in a workbook module and use the following syntax =SumColors(Range,Color number) , e.g. =SumColors(A1:A9,3) will sum all the cells in A1 to A9 if they are colored red.

Some caveats :

1. Conditional formatting is ignored, the underlying color of the cell is all that matters.
2. If you change the font colors of one of the numbers you must recalculate manually (F9) as Excel does not do recalculations automatically for changes in formatting.

If you want to sum cells based on a color applied by conditioanl formatting, then try to use a SUMIF with the same criteria.

Andrew

4. Re: CONDITIONAL

Andrew,
I'd suggest changing the return type of the function from integer to double - i.e. Function SumColors(R As Range, Col As Integer) As Double - otherwise it's a bit limited.

5. Re: CONDITIONAL

<img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>

Quite correct Rory, but I just plagiarised it from Hans so we can blame him

Thanks

Andrew

6. Re: CONDITIONAL

he was counting though.... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
(it was nice to see that I'm not the only one that does things like that!)

7. Re: CONDITIONAL

OK...well I guess I am one BIG DUMMY. I put the code exactly as you suggested in a worksheet module. Filled in some sample data as you described (a1:a9) and put the code (=SumColors(A1:A9,3))...but all I get is "#Name?". Any suggestions?

Thanks for your help, I really do appreciate it.

8. Re: CONDITIONAL

Andrew, thanks for the code. I added it to my personal.xls file just in case I need it some day, and I probably will. Could you post the code for doing a count instead of a sum? Thanks.

9. Re: CONDITIONAL

Try putting in a general module, the place where recorded macros are located. In the VB editor, select the workbook you want the code in, go to Insert, Module, and place it there. Change the first line to (See Rory's post above) :-
<pre> Function SumColors(R As Range, Col As Integer) As <font color=red>Double</font color=red></pre>

Hope that works for you. ( and remember always that it will not automatically update)

Andrew

10. Re: CONDITIONAL

As I mentioned before, You can change my code as you wish, but don't blame me for it ... (just joking)

I was just wondering why you were changing the return of the function to a 'double' type in case of the CountColors function: if you count the cells with a specific interior color then the result is an integer or if you have plenty to count you can change it to 'long'.
In case you want to sum the contents of the cells with a specific interior color, then it is quite obvious that you change the return value to 'double' as Excel cells are all by default of the type 'double'. You can easily check that with the Vartype command from VBA.

So, Rory, use my code, but when someone changes it, blame the one who made the changes... Sorry Andrew..

11. Re: CONDITIONAL

Hans,

I didn'd change the return of CountColors, but unfortunately omitted to change it when the function changed from counting to summing. I just copied and pasted, I intended removing AS Integer, as clearly it is not suitable for a general purpose SUM function, but after making the other changes I just forgot. I should have composed the thing from scratch I suppose.

Andrew

12. Re: CONDITIONAL

Andrew,

I've just read through this CONDITIONAL thread, by accident, and saw my name (very nice of you to mention the original author), that's why I couldn't stand to post a reply, or, is it because it's a quiet Saterday afternoon and I was tired of working in the garden, now that it stopped raining?

13. Re: CONDITIONAL

Andrew, thanks a lot (again)!

14. Re: CONDITIONAL

Hi Andrew,

I was copying these *very useful* functions into my personal library when I noticed two typos. So in case anyone else wants to do the same I thought I'd let you know.

<pre>Function CellColorCount(fRange As Range, fCol) As Long
Application.Volatile True
Dim Rng As Range
For Each Rng In fRange
If Rng.Interior.ColorIndex = fCol And Rng.Value <> "" Then
ColorCount = ColorCount + 1
End If
Next
End Function
</pre>

<pre>
Function CellColorSum(fRange As Range, fCol) As Double
Application.Volatile True
Dim Rng As Range
For Each Rng In fRange
If Rng.Interior.ColorIndex = fCol Then
ColorSum = ColorSum + Rng.Value
End If
Next
End Function
</pre>

ColorSum = ColorSum + Rng.Value
ColorCount = ColorCount + 1

CellColorSum = CellColorSum + Rng.Value
CellColorCount = CellColorCount + 1

I appreciate all the effort you put in here at the Lounge to help out those less knowledgeable like myself.

Ken

P.S. What does Application.Volatile True do?

15. Re: CONDITIONAL

Thanks Ken, thats what I get for not being attentive - I changed the names of the functions and forgot the obvious changes that should have been made in the code.

The Application.Volatile statement is to ensure that Excel recalculates the function any time a change is made to the sheet that involves recalculation. Strictly speaking it is not necessary as a function is recalculated any time any of it's arguments change. However in the case of these functions no calculation will take place if the color of a cell or font changes, as Excel does not do a recalculation based on formatting events. So if the volatile method is invoked it helps to keep the result more up to date if some other unconnected calculation takes place.

Sorry for any trouble caused,

Andrew

Page 1 of 2 12 Last

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•