CONDITIONAL

Topic

New Reply

HELP PLEASE !!!!!!

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

THANKS

Reply | Quote

Replies

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?

Reply | Quote

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.

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

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

Reply | Quote

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.

Reply | Quote

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

Thanks

Andrew

Reply | Quote

he was counting though….
(it was nice to see that I’m not the only one that does things like that!)

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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) :-

	Function SumColors(R As Range, Col As Integer) As Double

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

Andrew

Reply | Quote

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.

Reply | Quote

Jim, sorry but your request for the count routine got overlooked. The equivalent code is at Countif.

However I am including a matching set that sum and count based on either font color or cell color.

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
            CellColorCount = CellColorCount + 1
        End If
    Next
End Function
 
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
            CellColorSum = CellColorSum + Rng.Value
        End If
    Next
End Function
 
Function FontColorCount(fRange As Range, fCol) As Long
    Application.Volatile True
    Dim Rng As Range
    For Each Rng In fRange
        If Rng.Font.ColorIndex = fCol And Rng.Value  "" Then
            FontColorCount = FontColorCount + 1
        End If
    Next
End Function
 
Function FontColorSum(fRange As Range, fCol) As Double
    Application.Volatile True
    Dim Rng As Range
    For Each Rng In fRange
        If Rng.Font.ColorIndex = fCol Then
            FontColorSum = FontColorSum + Rng.Value
        End If
    Next
End Function

These functions only update when the worksheet is recalculated (F9). As a change in formatting does not trigger excel’s recalc, they will not be updated purely on a color change. The following code may help get round that problem, if it is suitable for your circumstances :-

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Calculate
End Sub

That code should be placed in the Workbook module of the project involved, and not in a general module.

Also note that the Count functions only count non-blank cells.

All are called by FunctionName(Range,ColorIndex code)

Hope they are of use.

Reply | Quote

Andrew, thanks a lot (again)!

Reply | Quote

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.

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

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

The lines reading

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

should read

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?

Reply | Quote

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

Reply | Quote