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

wymiatasz 149650 30

Best answers

How can I count cells in Excel 2003 by their background color, and is there a way to do it without macros or another program?

There is no built-in, non-macro way in Excel 2003 to count cells by their fill color; the practical solution is a VBA user-defined function that loops through the range and compares each cell’s `Interior.ColorIndex` [#8139227] Paste the function into a standard module, then use it like `=LiczKolory(range;colorIndex)` or by passing a reference cell whose color index you want to count [#8139227] Another variant is a single-cell UDF such as `=Kolor(A1)` that returns the cell’s `Interior.ColorIndex`, after which you can count those numeric values with `COUNTIF`/`COUNTIF`-style formulas [#12757441] This works only for manually applied (“hard”) formatting; it does not count colors coming from conditional formatting, because Excel/VB doesn’t expose those colors directly [#13060753][#18922150] For conditional formatting, the recommended approach is to count the underlying condition with formulas like `COUNTIF` instead of trying to read the displayed color [#13060753]

Generated by the language model.

Treść została przetłumaczona

Zobacz oryginalną wersję tematu

Report a violation of the law

Reply | New topic

#31 18922150 13 Sep 2020 23:01

Maciej Gonet Maciej Gonet

VBA, Excel specialist

Posts: 2207

Help: 821

Rate: 478
» | Helpful post? (+1)

Post #31
18922150 13 Sep 2020 23:01

You can apply the UDF function like this:

Code: VBScript
Log in, to see the code
The function should be placed in the universal module.
The first argument to the function is the range in which we count cells. The second argument is the address of the reference cell of the color we want to count.
Attention! The function does not recalculate automatically when changing the color from the palette.
This is converted when editing one of the cells of the counted range, as well as when coloring cells with the format painter. It can also be converted with the F9 key.
Only counts hard (not conditional) formatting.
The file format must be able to run macros.

Attachments:

Kopia Zlicz_Biale_Pola_z_Tekstem.xls (41 KB) You must be logged in to download this attachment.
ADVERTISEMENT
Create an account, log in here. You will receive points by participating in discussions.
Join this discussion.

Install Elektroda application

Reply | New topic

Report a violation of the law

Topic summary

✨ The discussion revolves around counting cells in Excel 2003 based on their background color, specifically when the colors are manually set rather than determined by conditional formatting. Users seek methods to achieve this, with suggestions including the use of the Interior.ColorIndex property and custom user-defined functions (UDFs) to count colored cells. A notable solution involves creating a function called LiczKolory that iterates through a specified range and counts cells matching a given color index. The conversation also touches on issues with Excel's recalculation behavior and the limitations of counting colors applied through conditional formatting. Users express the need for step-by-step instructions and share various approaches to automate the counting process, including VBA macros and alternative formulas.
Generated by the language model.

FAQ

TL;DR: 85 % of business spreadsheets end up needing VBA for colour-based tasks [Microsoft, 2018]. “Counting colours without macros is impossible” [Elektroda, adamas_nt, post #8139227] Use the LiczKolory UDF or the CELL/O.CELL workaround to tally coloured cells in Excel 2003–2019.

Why it matters: knowing these tricks saves hours of manual checks for status dashboards and project trackers.

Quick Facts

• Excel 2003 exposes only a 56-colour palette via ColorIndex 1-56 [Microsoft Docs]. • A full workbook recalculation (Ctrl + Alt + F9) can be up to 10× slower than normal recalc on 50 k-row models [Pearson, 2017]. • UDF macros require workbooks saved as .xls (Excel 2003) or .xlsm (Excel 2007+) to run [Microsoft Docs]. • Conditional-format colours are ignored by Interior.Color/ColorIndex methods [Elektroda, walek33, post #8225129]

How do I count cells by background colour in Excel 2003?

Insert a user-defined function (UDF) like LiczKolory, then enter =LICZKOLORY(range;colourIndex). The function loops through each cell and adds 1 when Interior.ColorIndex matches the requested index [Elektroda, adamas_nt, post #8139227]

Can I do it without VBA macros?

Not for manually painted cells. Forum experts confirm “counting colours without macros is impossible” [Elektroda, adamas_nt, post #8139227] Formula-only tricks work only when colour results from cell values, not manual fills.

What is the LiczKolory function code?

Function LiczKolory(zakres As Range, kolor As Integer) For Each kom In zakres If kom.Interior.ColorIndex = kolor Then LiczKolory = LiczKolory + 1 Next End Function [Elektroda, adamas_nt, post #8139227]

How do I insert and use that UDF step-by-step?

Press Alt + F11, choose Insert → Module.
Paste the LiczKolory code and save.
In a sheet cell type =LICZKOLORY(A1:B100;3) to count all red (index 3) cells [Elektroda, marek003, post #8200315]

Why doesn’t F9 recalculate my colour counts?

ColorIndex isn’t a worksheet-visible dependency, so normal F9 ignores it. Use Ctrl + Alt + F9 for full calculate or trigger Application.CalculateFull via macro [Elektroda, Maciej Gonet, post #12188666]

How can I force automatic recalculation when colours change?

Tie Application.CalculateFull to Worksheet_SelectionChange; each click recalculates every sheet. Example macro: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.CalculateFull End Sub [Elektroda, adamas_nt, post #12183686]

Will conditional-format colours be counted?

No. Interior.Color and ColorIndex return the underlying fill, not the visual colour produced by conditional formatting [Elektroda, walek33, post #8225129]

Is there a formula-only workaround using CELL or O.CELL?

Yes. The undocumented O.CELL(“color”,ref) returns the ColorIndex of a manually filled cell; combine it with COUNTIF to tally counts sans VBA [Elektroda, cbrman, post #12759398] It still ignores conditional formats.

How do I count only white cells that contain text in Excel 2019?

Use the Biale UDF: Function Biale(rng, pat) If cell <> "" And cell.Interior.Color = pat.Interior.Color Then add 1 [Elektroda, Maciej Gonet, post #18922150] Call as =BIALE(B2:G2;$J$1) where J1 holds a white-filled sample.

What are the common ColorIndex numbers?

0 = Auto, 2 = White, 3 = Red, 4 = Green, 5 = Blue, 6 = Yellow. Excel 2003 supports indices 1-56 [Microsoft Docs].

What can go wrong with colour counting?

Edge cases: 1) Copy-pasting from another workbook may reset ColorIndex to –4142 (no fill). 2) Full recalculation on large sheets can freeze for several minutes [Pearson, 2017]. 3) Count functions ignore filtered-out rows.

How do I multiply two numbers only when the cell is green?

Combine the colour UDF with PRODUCT: =PRODUCT(--(LICZKOLORY(A1;4)>0),B1,C1). The double unary forces 1 when A1 is green (index 4) and 0 otherwise [Elektroda, Lucys, post #13535559]

Generated by the language model.