logo elektroda
logo elektroda
X
logo elektroda
Advanced Search
logo elektroda

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.
ADVERTISEMENT
Treść została przetłumaczona polish » english Zobacz oryginalną wersję tematu
Report a violation of the law
Reply | New topic
  • #1 8135373
    wymiatasz
    Level 2  
    Posts: 4
    Rate: 11
    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
  • ADVERTISEMENT
  • #2 8135628
    yogi009
    Level 43  
    Posts: 14651
    Help: 848
    Rate: 2645
    And is this background the result of some logical condition? Maybe follow this track?
  • #3 8135684
    wymiatasz
    Level 2  
    Posts: 4
    Rate: 11
    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"
  • ADVERTISEMENT
  • #5 8137401
    wymiatasz
    Level 2  
    Posts: 4
    Rate: 11
    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 ;) .
  • Helpful post
    #6 8139227
    adamas_nt
    VIP Meritorious for electroda.pl  
    Posts: 5320
    Help: 1508
    Rate: 658
    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.
  • #7 8139374
    wymiatasz
    Level 2  
    Posts: 4
    Rate: 11
    And finally it's ok :) . Thanks a lot for your help!
  • #8 8199999
    adrian2202
    Level 1  
    Posts: 1
    Rate: 3
    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?
  • #9 8200315
    marek003
    Level 40  
    Posts: 4598
    Help: 799
    Rate: 483
    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
  • #10 8223865
    ktr
    Level 10  
    Posts: 89
    Rate: 11
    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 :)
    Attachments:
    • test.xls (13.5 KB) You must be logged in to download this attachment.
  • #11 8225129
    walek33
    Level 29  
    Posts: 1015
    Help: 132
    Rate: 81
    ktr wrote:
    I also used conditional formatting

    And here was the problem because
    Code: text Expand Select all Copy to clipboard
    kom.Interior.ColorIndex
    will not count conditional formatting colors.
  • #12 8230126
    ktr
    Level 10  
    Posts: 89
    Rate: 11
    Can anyone help me with my problem? I'm pretty green in Excel.
  • ADVERTISEMENT
  • #13 8660099
    klonik
    Level 2  
    Posts: 3
    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.
    Excel 2003: Counting Cells by Background Color - Identify & Calculate Colored Cells in Spreadsheets
    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 ??
  • #14 12182146
    mundeczek88
    Level 11  
    Posts: 28
    Rate: 2
    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?
  • #15 12183384
    adamas_nt
    VIP Meritorious for electroda.pl  
    Posts: 5320
    Help: 1508
    Rate: 658
    mundeczek88 wrote:
    F9 has no effect
    And it should (?) As a last resort, paste into the spreadsheet code
    Code: text
    Log in, to see the code
    The sheet will be recalculated each time the active cell changes.
  • #16 12183500
    mundeczek88
    Level 11  
    Posts: 28
    Rate: 2
    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:
    Excel 2003: Counting Cells by Background Color - Identify & Calculate Colored Cells in Spreadsheets

    In the Worksheet Code:
    Excel 2003: Counting Cells by Background Color - Identify & Calculate Colored Cells in Spreadsheets

    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?
  • #17 12183686
    adamas_nt
    VIP Meritorious for electroda.pl  
    Posts: 5320
    Help: 1508
    Rate: 658
    Try to convert them all. Replace the code with:
    Code: text
    Log in, to see the code
    But it probably won't help. All that remains is F2> Enter, or write a more elaborate macro.
  • #18 12186858
    mundeczek88
    Level 11  
    Posts: 28
    Rate: 2
    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.
  • #19 12187185
    marek003
    Level 40  
    Posts: 4598
    Help: 799
    Rate: 483
    :arrow: 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
  • #20 12188666
    Maciej Gonet
    VBA, Excel specialist  
    Posts: 2207
    Help: 821
    Rate: 478
    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!
  • #21 12189020
    marek003
    Level 40  
    Posts: 4598
    Help: 799
    Rate: 483
    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]) ?.
  • #22 12189220
    Maciej Gonet
    VBA, Excel specialist  
    Posts: 2207
    Help: 821
    Rate: 478
    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.
  • ADVERTISEMENT
  • #23 12757441
    sushuang
    Level 2  
    Posts: 3
    Rate: 3
    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):

    Code: text Expand Select all Copy to clipboard
    Function Kolor(Adres As Range)

    Application.Volatile True

    Kolor = Adres.Interior.ColorIndex

End Function


    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
  • #25 13059628
    maryna 76
    Level 11  
    Posts: 10
    Rate: 13
    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
  • #26 13060753
    Maciej Gonet
    VBA, Excel specialist  
    Posts: 2207
    Help: 821
    Rate: 478
    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.
  • #27 13063737
    maryna 76
    Level 11  
    Posts: 10
    Rate: 13
    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%
  • #28 13535559
    Lucys
    Level 1  
    Posts: 1
    Rate: 2
    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?
  • #30 18921121
    mieszko I
    Level 18  
    Posts: 483
    Help: 2
    Rate: 71
    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).

    Excel 2003: Counting Cells by Background Color - Identify & Calculate Colored Cells in Spreadsheets
    Attachments:
    • Zlicz_Biale_Pola_z_Tekstem.xlsx (11.38 KB) You must be logged in to download this attachment.
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?

  1. Press Alt + F11, choose Insert → Module.
  2. Paste the LiczKolory code and save.
  3. 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.
ADVERTISEMENT