Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: CONDITIONAL

  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CONDITIONAL

    HELP PLEASE !!!!!!

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

    THANKS
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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!)
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    Star Lounger
    Join Date
    Dec 2000
    Location
    Temple, Texas, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Jim Whitt
    Pharmacist
    Temple, Texas

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #12
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #13
    Star Lounger
    Join Date
    Dec 2000
    Location
    Temple, Texas, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CONDITIONAL

    Andrew, thanks a lot (again)!
    Jim Whitt
    Pharmacist
    Temple, Texas

  14. #14
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>


    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?

  15. #15
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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 LastLast

Posting Permissions

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