Microsoft → [Excel] COUNTIF Counting Cells by Color

I have a spreadsheet that I asked for help on about a year ago on here and received a lot of great help. I have looked around and can't really find and answer to a change I want to make to the same spreadsheet.

What I want to do is change the counting of cells from whether there is a number in the cell to if the cell is a certain color due to a web query.

=COUNTIF(B117:M117,">150")
=COUNTIF(Q45,">80")-COUNTIF(Q45,">90")

So instead of doing that I want to count how many are red instead.

I have found some examples on the net but it looks like they are all VBS scripts, which I don't know how to do.

Any help would be appreciated,
Geek

I guess I jumped the gun to soon. I found a great website that showed me how to create a function in excel then another that had a function already.

»www.cpearson.com/excel/c ··· lors.htm

If anyone has wondered... Sorry about taking up the board space.

Geek

Thanks for the link. Actually, I had been wondering about that, and your link hit the target directly. Thanks again!

It sounds like a calculation/updating issue of some sort. Have you tried an F9 manual recalculate right after loading the sheet?

Yes F9 works, but that isn't a real fix.

Here is the function:

Function CountByColor(InRange As Range, _
    WhatColorIndex As Integer, _
    Optional OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True
 
For Each Rng In InRange.Cells
If OfText = True Then
    CountByColor = CountByColor - _
            (Rng.Font.ColorIndex = WhatColorIndex)
Else
    CountByColor = CountByColor - _
       (Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng
 
End Function
 

Is there something I can add delete change or otherwise to get it to automatically update? Yes in Tools > Options > Calculation it is set to Automatic.

Geek

From the link you cited:

"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."

It sounds like this is what is happening. Some cell cosmetic attributes (usually fonts, colors, etc) are designed not to trigger Excel to recalculate if they're changed, nor will they trigger Excel's Worksheet_Change event flag to allow/cause VBA routines to be notified that such a change has occurred. Hence, there's no place for either Excel or an added function to automatically "get a grip" in finding out such kinds of cell attributes have changed. This forces the user to manually do a recalculation following whatever actions may have changed a cell's cosmetic attribute. If you were using a macro of some sort to do the downloading or updating that causes the color change in cells, then of course, you could insert a "recalc" command after the downloading, near the macro's end... that would set up the sheet to recognize the post-download state of affairs regarding any changed cell colors.

edits: corrected flag title, clarity

I didn't see that on the page sorry. This spreadsheet is on a remote file server and about 1-5 people at a time could be in it. How do you add a "recalc" to a web query? I don't know where it would store the data in excel? I see you can store a query locally, but that doesn't help the other people use it.

Geek

I'm not sure that you can implement any kind of recalc command within a webquery .iqy file.

Usually the values accessed by a spreadsheet from a website via a webquery are transferred into a spreadsheet to some kind of target data table. If that transfer involves numerical or textual alterations to existing cell-values in the targeted storage range, that overwriting should of itself trigger an auto-recalculation of the sheet (assuming it's turned on), regardless of whether additional cell cosmetic attributes are involved. Sometimes, it may be necessary to have a simple dummy formula in a cell somewhere outside the data table refer directly to the values of all data-table cells in such a way that a change to any data table value forces the results of the dummy equation to change, and sheet recalculation can be forcibly auto-triggered in that way. (eg: =sum(first-table-cell-address.last-table-cell-address) Even if the dummy equation evaluated incorrectly or to an error, recalc should get triggered if the data in the table range changes.

Is the data being downloaded via web-query to the spreadsheet purely of a cosmetic nature, at the cell level of each cell?
edits: clarity... I hope!

OK... once you select and accept it, the data selected in the webquery has to be transferred by Excel to a range of data values somewhere in the spreadsheet. That full target range is where the spreadsheet accesses the now-imported values... and that's the table that you'll be concerned with. Based on your screenie, try applying the dummy equation from my earlier post, which would now look something along the lines of:
=sum(b5.last-table-cell-address)
to some empty cell outside the data table, and see if a data-query import doesn't now trigger an auto-recalc of the sheet. (Assuming, of course, that b5 is where the first table value that would ever be downloaded actually resides.)

I don't have time to dig into this in real detail right now - won't have until this weekend. But a couple of comments that might be of assistance.

First, the Chip Pearson site is probably one of the best regarding dealing with colors/formats of cells being used to provide a count. The code is solid.

Second, the 'Application.Volatile = True' statement makes sure that the function(s) are executed each time the sheet itself is recalculated by Excel for whatever reason.

Third, if you've got ranges that are constantly changing, you may want to take a look at Debra Dalgliesh's Contextures.com site »www.contextures.com/ and look for her assistance in dealing with dynamic ranges. A wealth of information about Excel to be found at her site also.
Table of Tips Contents page: »www.contextures.com/tiptech.html
Detailed instructions on creating and using a Named Dynamic Range:
»www.contextures.com/xlNa ··· #Dynamic

Good luck.

Thanks everyone for the help, I can't seem to get it to automatically calculate the data. I am ok with the F9 solution since it does work and it easy to put a reminder on the top of the spread sheet to do it when opening the sheet after an update.

Geek