Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Statement based on range + fill color (Excel 2000)

    Is it possible to construct an IF or IFSUM statement that checks the following:

    If cell is in range A1:Z100 and fill color is yellow, add to sum in this cell.

    Thanks for your help!!
    Troy

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Conditional Statement based on range + fill color (Excel 2000)

    Hello Troy,

    There's no standard worksheet function for testing cell colours. For that you need a User-defined Function (UDF). However, if the cell is coloured via conditional formatting, it should be possible to use the same logic that determines the format as part of your summing equation.

    If you need a UDF, here's a vba routine to get you started - it simply counts the number of yellow cells in a selection:

    Sub CountYellow()
    Dim ColourCount as Integer
    Dim ColouredCell as Range
    ColourCount = 0
    For Each ColouredCell In Selection
    If ColouredCell.Interior.ColorIndex = 6 Then ColourCount = ColourCount + 1
    Next ColouredCell
    MsgBox ColourCount
    End sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Statement based on range + fill color (Excel 2000)

    That gets me a lot of the way there. I have done a lot of Word macros, but Excel is a different bird.

    I want to take what I get from the following:
    ------------------------------
    Public Sub CountYellow()
    Dim ColourCount As String
    Dim ColouredCell As Range
    Dim SumYellow As DataObject
    ColourCount = ""
    For Each ColouredCell In Selection
    If ColouredCell.Interior.ColorIndex = 6 Then ColourCount = ColourCount + "+" + ColouredCell.Address
    Next ColouredCell
    Application.Goto Reference:="R15C3"
    MsgBox ColourCount

    End Sub
    --------------------------
    Instead of adding it to a MsgBox, I want to put it into the following formula:

    =-(ColourCount)

    AND

    Put this formula into a specific cell in the current worksheet.

    How do I do that?

    Thanks!!
    Troy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Statement based on range + fill color (Excel 2000)

    To return a result, you must change the Sub to a Function, and assign the return value to the function name:

    Public Function CountYellow(oRange As Range) As Long
    Dim oCell As Range
    CountYellow = 0
    For Each oCell In oRange.Cells
    If oCell.Interior.ColorIndex = 6 Then
    CountYellow = CountYellow + 1
    End If
    Next oCell
    End Function

    Use in a cell formula like this:

    =CountYellow(A1:A100)

    See Functions For Cell Colors on Chip Pearson's site for more general functions involving colors.

  5. #5
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Statement based on range + fill color (Excel 2000)

    Hans,
    Thanks for the help and especially the link. I found exactly what I was looking for with the Function SumByColor.

    Troy

  6. #6
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Statement based on range + fill color (Excel 2000)

    Ooops!! I spoke too soon. I'm almost done.

    I got everything to work as I wanted, except one thing: I noticed that when I put the function in a cell, it calculated everything perfectly. However, if I changed a value affected by the function (or in this case added yellow fill to another cell), the function did not recalculate on its own. If I went into the cell, and pressed ENTER, all was fine.

    Is there a way to get this function to calculate every time a change is made to a cell in the sheet?

    I feel like this is such a newbie question, but hey, I just haven't worked a lot with Excel.

    Thanks again!!
    Troy

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Statement based on range + fill color (Excel 2000)

    This is not a naive question, it is a genuine problem. As Chip Pearson notes in the web page I referred to in my earlier reply:
    <hr>NOTE: When you change the background or font color of a cell, Excel does not consider this to be changing the value of the cell. Therefore, it will not recalculate the worksheet, nor will it trigger a Worksheet_Change event procedure. This means that the values returned by these functions may not be correct immediately after you change the color of a cell. They will not return an updated value until you recalculate the worksheet by pressing ALT+F9 or by changing the actual value of a cell. There is no practical work-around to this. You could use the Worksheet_SelectionChange event procedure to force a calculation, but this could have a serious and detrimental impact on performance.<hr>
    The result of SumByColor (and similar functions) will be updated whenever a calculation occurs anywhere on the sheet - i.e. when you enter or edit a value or formula, or press F9 to recalculate. If you change the fill color of a cell, the formula will not be updated automatically.

  8. #8
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Statement based on range + fill color (Excel 2000)

    I found a clunky solution. I have a macro I use to add fill to a cell. The fill is checked by this function.

    So an obvious solution is to add "Calculate" after the command to add the fill.

    Looks like this:

    With Selection.Interior
    .ColorIndex = 6
    .Pattern = xlSolid
    End With
    Calculate


    Just thought I'd pass it on.

    Thanks everyone for your help!!
    Troy

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Conditional Statement based on range + fill color (Excel 2000)

    Hi Troy,

    As noted in the text quoted by Hans, "You could use the Worksheet_SelectionChange event procedure to force a calculation". If you combine that with a target range test that limits the trigger range to just the cells you want to test, the impact on the worksheet's performance can be minimised. While you way works if the macro is used to fill the cell, it won't otherwise. Here's some code to try:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, ActiveSheet.Range("A1:Z100")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    ActiveSheet.Calculate
    Application.EnableEvents = True
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. #10
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Statement based on range + fill color (Excel 2000)

    Thanks for the information!! However, I now seem to be having the opposite problem.

    I'm not sure why, but now this function that previously required a manual Calculate from me, no longer does. It is running every time a type something in a cell and leave the cell (every time there is a change). It is really annoying.

    Any ideas why this is happening and what I can do to keep this SumByColor function from running with every change?

    As I said, I included code in the macro I use to color the cell to do the calculation. That works fine for me.

    Thanks!!
    Troy

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Statement based on range + fill color (Excel 2000)

    The code macropod provided will recalculate the worksheet every time you move to another cell in the range A1:Z100. If you don't want that, you shouldn't use the code.

  12. #12
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Statement based on range + fill color (Excel 2000)

    Can you give me an alternative?

    I have set the applicaiton.volitive to false, but it still tries to calculate every time I make a change to a cell.

    Please advise.

    Thanks!!
    Troy

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Statement based on range + fill color (Excel 2000)

    The worksheet_selection code runs whenever a new selection is made.

    Delete the worksheet_selection code if you don't want any code to run or remove the calculation line from the worksheet_selection code to prevent the worksheet from calculating all the time.

    Changing the application.volatile only prevents this function from being updated on every calculation. the sheet recalculation is still being done due to the code.

    Steve

  14. #14
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Statement based on range + fill color (Excel 2000)

    Thanks for the information on the application.volatile. That helps me understand why this has no effect.

    However, I am confused by your comments on "worksheet_selection". I don't have any such code in the function itself.

    The following is the code I am using from the Functions For Working With Cell Colors at http://www.cpearson.com/excel/colors.htm.
    -------------------------------------
    Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
    Optional OfText As Boolean = False) As Double
    '
    ' This function return the SUM of the values of cells in
    ' InRange with a background color, or if OfText is True a
    ' font color, equal to WhatColorIndex.
    '
    Dim Rng As Range
    Dim OK As Boolean

    Application.Volatile True
    For Each Rng In InRange.Cells
    If OfText = True Then
    OK = (Rng.Font.ColorIndex = WhatColorIndex)
    Else
    OK = (Rng.Interior.ColorIndex = WhatColorIndex)
    End If
    If OK And IsNumeric(Rng.Value) Then
    SumByColor = SumByColor + Rng.Value
    End If
    Next Rng

    End Function
    -------------------------------------
    In the cell with the function I am using the following:

    -----------------------------------------
    =SUMBYCOLOR(A1:A10,3,FALSE)
    -----------------------------------------

    Please advise what you mean by "worksheet_selection".

    Thanks!!
    Troy

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Statement based on range + fill color (Excel 2000)

    That function contains the line

    Application.Volatile True

    If you don't want the function to update automatically when the sheet is recalculated, remove that line.

    Macropod posted some code for the Worksheet_SelectionChange event; this code was supposed to go into the code module associated with the worksheet. If you have done that, you should remove the code.

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
  •