# Solved: colorsum and conditional format

Discussion in 'Business Applications' started by mrl, Mar 31, 2012.

Not open for further replies.

Joined:
Aug 12, 2007
Messages:
335
Hi

Im trying to keep a running total of bill payments.

Im using conditional format to change the color of the payment to red
on the day its due and keep it that color till the end of the month
Each month I will start a new sheet

In module 1, I have the following code:
HTML:
```Function ColorSum(ColorCell As Range, SumRange As Range) As Variant
Application.Volatile
Dim Cell As Range
For Each Cell In SumRange
If Cell.Font.ColorIndex = ColorCell.Font.ColorIndex Then
ColorSum = Cell + ColorSum
End If
Next
ColorSum = ColorSum
End Function
```
In the total cell I have

HTML:
`=colorsum(C1,B5:B30)`
Where C1 is the reference color (red)
Since colorsum won't recognize colors that were made with conditional formatting
Is there a different formula I can use that will recognize the change of color based on conditional format?

Im using
HTML:
`=day(today())>=C5`
For conditional format where row C is the due date and the conditional format formula is in B:5
In B6 its

HTML:
`=day(today())>=C6`
and so on

mike

Joined:
Mar 27, 2008
Messages:
6,608
First Name:
Hans
Maybe I'm not reading this right, but if you have the formulas why use the colorsum for the calculations?
I mean, the conditional format is the visual attention but the calculations you can do with the formulas you have shown here

Joined:
Aug 12, 2007
Messages:
335
Hi Hans,
Efan helped me with using conditional format to turn the number red depending on the date and keep it red till the end of the month. Thanks, efan
I want to keep a running total of the red numbers as the month progresses which i will subtract from money on hand. I didn't write that part of the formula for clarity.

Using the colorsum function (?) I can add only the red numbers in the column, but it doesn't recognize the red number if it was changed by conditional format. It does recognize the red number if i manually change it, which I'm doing now.

I'm wondering if I'm using colorsum properly or maybe i should be using a different formula. after posting the question, I thought of trying to use a sumif(.. .to only add the numbers from "today" on, but I've never used it, so i don't know how to write it.

Each month there willl be a new sheet and may have different amounts and/or dates.

Mike

Joined:
Aug 12, 2007
Messages:
335
Hi
First my sincere apologies to etaf for misspelling his name

After readying Keelellahs response, I played with an if formula to see if I could use it instead of conditional format

I used the following to copy the amount to a column out of view:

=if(b5=day(today())>=c5,+b5,)

The total cell has the sum of the new column

Each month I only have to copy the whole thing to a new sheet and do any changes to the amounts and/or dates

This is easier than having the color change and having only the new color added up.

Im posting this for people like me that use this site for help with excel.

Thanks for all the input

Mike

5. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,294
First Name:
Wayne
thanks for taking the time to let us know your solution,
it will be useful to people searching the forum

As Seen On