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

Topic

New Reply

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

Reply | Quote

Replies

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
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Troy

Reply | Quote

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

Reply | Quote

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:[indent]

---

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.

---

[/indent]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.

Reply | Quote

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

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

I never added the Worksheet_SelectionChange event code.

I also changed the Application.Volitale code to False.

Why would it still be calculating after every change to the worksheet?

Thanks!!
Troy

Reply | Quote

Troy,
My experience is that Application.Volatile False doesn’t work (certainly not reliably). It is better to comment out or remove that line entirely.
HTH.

Reply | Quote

I have tried this too, but it does not make a difference.

Any other ideas?

Thanks!!
Troy

Reply | Quote

If you change the value of one of the cells that contribute to the result of SumByColor, the function will recalculate automatically, but not if you change the color of one of those cells. That would require an explicit recalculation. If you don’t want the result to be updated if the cell values change, you should set calculation to Manual in the Calculation tab of Tools | Options…

Reply | Quote

So there is no way to just have specific cell’s not recalculate?

I’d hate to turn automatic calculation off for the whole sheet. I am using this for a budget and the other calculations work fine and quick. It is just running this funciton that seems to slow things down.

By the way, even if I make a change to the cells outside the range specified by the SumByColor funciton, it still runs the macro. I don’t know why. I have removed the function from VB and confirmed that things return to normal when I do so, so I know it is the funciton.

THanks!!
Troy

Reply | Quote

As far as I know, you cannot turn off automatic calculation for specific cells, it is a property of the Excel application as a whole.

As an alternative, you could create a extra column with values that define the “status” of a row, and use a SUMIF formula. If desired, you could use conditional formatting to color cells based on this column. That might be more efficient than using the SumByColor function. See the attached very simple example.

Reply | Quote

Perhaps I am misunderstanding your suggestion, but it sounds like you are saying I should apply color based on certain conditions. I am actually trying to do the opposite. I am trying to do a sum based on whether I have colored the cells.

Perhaps I could change the function to a macro that I could run at will. What do you think?

Thanks again for all the help!!
Troy

P.S. Happy 4th of July!!

Reply | Quote

I assume that coloring the cells has some meaning. This meaning could be expressed as well by entering a value in an extra column.

Using a macro instead of a custom function would be a viable alternative too.

Reply | Quote

Troy,
Perhaps something like the attached slight variation on what Hans posted will work for you. If you enter (or delete) an x in column B, the totals update automatically and the cells are coloured yellow via conditional formatting. This seems to be pretty much what you are doing now since you are colouring them manually anyway.
HTH.

Reply | Quote

Unfortunately the purpose of my macro is not to color the cell, but sum the values of all cells with a certain color.

The following is the code I have so far:

Public Sub DiscoverTroyColor()
'Public Sub DiscoverTroyColor(InRange As Range, WhatColorIndex As Integer, _
    Optional OfText As Boolean = False)
    Dim InRange, Rng As Range
    'Dim Rng As Range
    Dim WhatColorIndex As Integer
    Dim OfText, OK As Boolean
    Dim SumByColor As String
    
    SumByColor = 0
    OfText = False
    WhatColorIndex = 6
    InRange = "(M6:CR43)"
    
    Application.Selection.Interior.ColorIndex = 6
    
    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
    MsgBox SumByColor
    Calculate
End Sub

My problem right now is that when it gets to the line

For Each Rng In InRange.Cells

I get the following error:

Run-time error ‘424’ Object Required

What am I doing wrong?

Thanks!!
Troy

Reply | Quote

Try changing the line

InRange = “(M6:CR43)”

to

Set InRange = Range(“M6:CR43”)

That will make InRange a range object instead of a string.

Reply | Quote

That did it. Thanks!! I am finally all set and satisfied.

I have provided below all my code for any one who is interested:

Long code fragment moved to attachment by HansV

Thanks everyone for your help!!
Troy

Reply | Quote