1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: colorsum and conditional format

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

Thread Status:
Not open for further replies.
Advertisement
  1. mrl

    mrl Thread Starter

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

    Keebellah Trusted Advisor

    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 :confused:
     
  3. mrl

    mrl Thread Starter

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

    mrl Thread Starter

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

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

    Mike
     
  5. etaf

    etaf Moderator

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

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1047401

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice