Excel 2003: Counting Cells by Background Color - Identify & Calculate Colored Cells in Spreadsheets

Hello,

I can't find anywhere, if someone came up with something like counting cells in excel (ver. 2003) by identifying the background color of cells ...

Anyone have any idea? and a proposal to use another program of this type?

best regards

And is this background the result of some logical condition? Maybe follow this track?

The "target" cell itself should count the number of cells that have a red background from the given range of cells. I just want to display the number of these cells as a simple number. Backgrounds of counted cells do not have conditional formatting in total, I define the background for a given cell manually, depending on my "external data".

It could also be in the form of e.g. displaying to another cell a numerical value corresponding to the color of the tested cell. Then I would just count these numbers.
A pathological example:
IF "cell A4"; "Red colour" ; "display 1"

Use:

And without macros, scripts, etc., it is impossible? ;)

If it is impossible, someone could write a more detailed instruction on how to use a similar formulation .

wymiatasz wrote:

And without macros, scripts, etc., it is impossible?

Unfortunately not.

You can use a user function similar to this You paste into a standard module and use it like an Excel function, i.e. You can find the palette on the net. You can also use the reference cell index as the second argument.

And finally it's ok . Thanks a lot for your help!

Please help me with the topic.
Just like the koeg you sweep, I have to count the number of cells marked in a given color, but I have no idea how to get there.
I am asking for a step-by-step instruction on how to do it because I have no idea, the color numbers were pulled down somewhere, but the macro created a new notebook with the color and its number, but I don't know how to refer to it. The formula you gave me won't start ..... eghhh I have to do it and I have no idea how?

After all, you have it described
You paste the macro into the module
(toolbars - visual basic - visual basic editor.
insert menu - module ... and paste the function code here)
You can close the editor.

Go to the worksheet and enter in the free cell e.g. = numbers of colors (A1: B100; 5)
according to the hint:
= number of colors (range; color index)
And you have a given color counted

Hello, I have a problem with how to do one task in Excel - please help me.

There are 3 columns in the Excel table: "task" (text, task description), "deadline" (date by which the task is to be performed), "status" (drop-down box: done, in progress, delayed, not started).

The "status" column is a normal function: validation -> List, where I have defined what I can select. I also used conditional formatting: when you select 'done' the cell is green, when 'in progress' is yellow, when 'delayed' is red - the rest, i.e. 'not started', is necessarily white, i.e. no formatting :)

I wanted to tweak the table by adding automatic checking if today's date is later than the deadline and if so - it would automatically force red in the status. This is as Condition 1 in conditional formatting, the problem is that even if you choose the status 'done' and the date is after the deadline, the color is red and it should be green. I don't know how to deal with e.g. the IF function.

To help understand my problem, I enclose a table

ktr wrote:

I also used conditional formatting

And here was the problem because will not count conditional formatting colors.

Can anyone help me with my problem? I'm pretty green in Excel.

It's nice to be solved, but I have a more complicated example. I have to choose the number of max. (= max (a1: a5)) Then, by conditional formatting, the cell with the largest number is to change the background color and font color. In cells B1: b5 there is a formula (= if (max (a1: a5) = a1 / 2; a1). This string should have X columns. Then in rows I should sum up how many cells in a given row have changed the background color.

The above solutions mean that in all lines I have a sum of 4 (if I choose the color number blue) and 0 if I choose any other color. I went around this by adding a "blank" column where the cell with max is the formula = if (max (block) = cell to the left; "max"; '' '). After this procedure, I count the cells in the line with the inscription "max" {= how many.if (block; "max")}. The effect is achieved but ..... Maybe someone made it up ??

adamas_nt wrote:

wymiatasz wrote:

And without macros, scripts, etc., it is impossible?

Unfortunately not.

You can use a user function similar to this

Code: text Expand Select all Copy to clipboard

Function LiczKolory(zakres As Range, kolor As Integer)
For Each kom In zakres
  If kom.Interior.ColorIndex = kolor Then
    LiczKolory = LiczKolory + 1
  End If
Next
End Function

You paste into a standard module and use it like an Excel function, i.e.

Code: text Expand Select all Copy to clipboard

=liczkolory(zakres;indeks koloru)

You can find the palette on the net. You can also use the reference cell index as the second argument.

Sorry to dig up the topic.
I am using Excel 2003.
I used a function, but unfortunately Excel doesn't want to recalculate the cells.
Only after clicking the cell, displaying the function on the bar and clicking Enter, the data is converted.
F9 has no effect, security level is average, automatic conversion in Excel turned on.
How to make Excel recalculate after every change in data?

mundeczek88 wrote:

F9 has no effect

And it should (?) As a last resort, paste into the spreadsheet code The sheet will be recalculated each time the active cell changes.

Thank you very much for the reply.
Well, maybe F9 should not.
I pasted, but no changes.
I don't know what's wrong at all.
In Module1 I have:


In the Worksheet Code:


I can just describe what the file looks like.
I have several sheets with colored row selections.
In the last sheet, a summary of how many lines of a given color are in total.
I only have the spreadsheet code in this last file.
Maybe that's it?

Try to convert them all. Replace the code with: But it probably won't help. All that remains is F2> Enter, or write a more elaborate macro.

Well, unfortunately you were right and it doesn't work
As for the more complex macro, unfortunately I do not know Visual Basic to write one.
All that is left for me is searching the web and empirical testing.
Thank you for your help.

adamas - there was a similar issue on the forum and you probably gave a solution to it yourself. It was definitely not an ordinary Calculate. I guess something like:
Application.CalculateFull

Although I checked and it doesn't work either. (Unless I add a macro to a button, [a macro with only "Calculate" code] but then "normal" calculate works as well)

So it is more about where to put this code, because the "change" of the sheet does not react to the color change of the cell

Good morning,
Regarding color counting, I would like to tell my colleagues that Excel will take into account the changes after pressing Ctrl + Alt + F9.
Good luck!

Maciej Gonet wrote:

Excel will take into account the changes when you press Ctrl + Alt + F9

You're right, but that's what the macro code in the macro does: Application.CalculateFull
But how to call it "automatically" (not through the button, but by changing the cell color with formatting [of course not conditional formatting because it's a completely different issue]) ?.

There is probably no other way than to attach it to the SelectionChange event. The change will not be automatic, but when you do something in the worksheet, you constantly select some cells, and when the change would come from the code, you have to remember to select a cell at the end.

Hello everyone,

I decided to write from the beginning to the end a solution to the problem with counting colors "for green". Perhaps someone will find it useful in the future.

1. Open an excel sheet

2. Go to File / Save As here, select from the Save as type list: Excel macro-enabled workbook and enter a name for your file. Then save it wherever you want.

3. Go to the Developer tab at the top of the spreadsheet and click on the icon on the left called Visual Basic. In the tree on the left, double-click on Module1 and paste the following content there (if module1 does not exist, right-click in the empty field under the word "this workbook" and insert module1):



Close the displayed windows (save changes) by returning to the spreadsheet.

FINISHED :)

How does it work now?

Example:

enter Jan in cell A1
in cell B1 enter = color (A1) => a numerical value should appear (press F9)

Change the color of A1 to red and press F9. The value should change.

How to count the values of all colored cells now. For example, I recommend the count if functions

eg for the green color the value is 43. In C1 we enter the number if (B: B; 43), that is, if the number 43 appears in column B, the sum of such cells will be by +1.

We color all cells in column A, press F9 and in cell C1 there is the number of such cells. Easy If you are not satisfied that the color values in column B are shown, I suggest you simply hide this column.

Best regards!

Su

Since the topic has been dug up, I will add that you can get the number of the manually assigned background color of the cell without the use of macros.
Details in the link below
http://www.apocotenexcel.pl/okom.htm

Argument list of the o.cell function
http://www.excelforum.pl/topics1/edytowanie-adresu-komorki-vt31631.htm#167495

already 4 hours sitting and the massacre please help Well, I have such a problem, and in the column a1 from a10, colors are displayed as a result of conditional formatting (higher by 50 in yellow and lower by 50 in blue, the average is 1000
And everything fits, but I mean how many background colors, i.e. gold and blue, are in a given range, e.g. in column a1 to a10 there is 2 yellow and 1 blue result 3 and this is what I have a problem Please help me, I can not do this formula and I am really thin with excel

It is difficult in Excel to count ex post colors given by conditional formatting, because in Visual Basic there is no access to the appropriate data. The only practical way is to refer to the formulas used in conditional formatting and count with appropriate formulas, e.g. COUNTIF, how many times the condition was met. More specific help requires an attachment.

Maciej, you're right, it turns out that I actually walked through the torment with colors on a yes-no basis and counting works 100%

Hello,

I am asking for a hint: what if I would like to use the formula of the product of two numbers, if a given column is e.g. green, is there a way to do it?

https://support.microsoft.com/kb/2815384 all you need is there

Excel 2019 - only count white cells and only with text.
From what I read, there is probably no such function available, will someone proficient in VBA help.
The point is for the function in the "Working days" cell to count (sum), only white (or selected color) cells with text only (color without meaning).