Comments on: How to sum and count cells by color in Excel

Even though Microsoft Excel has a variety of functions for different purposes, none can count or sum by color of a cell. Aside from third-party tools, there is only one possible solution - create your own functions. Continue reading →

Comments page 2. Total comments: 339

  1. CountCellsByColor works fine using the Excel application (on Windows 10) but when shared through a link and viewed from a browser I get the #NAME?.
    After removing the line containing "Application.Volatile" it also works fine from a browser view.

      1. Thanks! understood. Nevertheless, I'm sharing in viewing mode only so no changes can be made and all Excel Online results look correct after removing the Application.Volatile line. Looks like the Excel Desktop calculated results have been saved and are shown in Excel Online without needing to run VBA(?)

        1. Hi! Application.Volatile causes Excel to recalculate the values of the custom function every time there is a change in the worksheet. Since it doesn't work in Excel Online, it causes an immediate error.

  2. Hi, the count cells by color funtions work great! But is it possible to combine these and count cells with specific fill and font colours, e.g. yellow cells with red font?

    1. Hi! To count yellow cells with red font, try using a formula like this:

      =SUM((GetCellColor(A1:A10)=65535)*(GetFontColor(A1:A10)=255))

      1. Thanks! That formula only seems to return a 1 or a 0. I've been able to achieve the desired result by using GetCellColor and GetFontColor in two new colums, then using COUNTIFS to count the required cominations. I'm thinking there must be a better way though.

  3. Hey, the code countscellsbycolor works fine but if I change a cell color the value of my code doesn't change. I have to clic on the code for the value to change.
    It is easier if the value change instantly so i don't have to clic on the code each time.

    1. Hey, Excel automatically recalculates formulas when a cell value changes. A color change is not considered a value change.

  4. I am using the SumCellsByColour whic is fantatic - thank you!

    Could anyone help advise how I could also add an additional if condition. Basically I want to add a coloured cell as long as another cell in my table also has specific text. e.g. sum the coloured cell as long as cell C2 = "include". Grateful for any advice or guidance that anyone can offer.

    1. Hello! Use the GetCellColor function to get the color of the cell. I believe the following formula will help you solve your task:

      =SUM((GetCellColor(D2:D20)=GetCellColor($K$1))*(J2:J20="text"))

      K1 - cell with color sample.

    2. Should have said here where I put C2, this would of course be a range in a table corresponding to the range where I'm looking at colour. So SumCellsByColour(table_col_1,colour) as long as table_col_2 = "specific text". Hope this is clearer.

  5. I tried using your Subcountbyconditionalformat, but I do not get the correct count. I created a calendar view that highlights Saturday & Sunday as gray based on the date in the cell. I also have a conditional format that states if the date is not within a certain month to leave the cell white. The counter seems to count any cell that has a conditional format that could make it gray instead of the end result of multiple conditional formats. Is this how it should operate?

    1. Honestly, this tool is amazing, not even chat GPT suggested a precise process as the one stated here.

      Thank you

    2. Hi! The function counts only cells with a certain color. Check your data and formulas. It is possible that the color is incorrectly specified in the formula.

  6. Hi all,

    I just added this sum function "=SumCellsByColor" in my workbook earlier and save it. it worked perfectly but when I open the same excel again after few days, how come it is not automatically calculating. I have no idea have to resolve the issue. Any help please on how I fix the problem? Thanks!

  7. The macro for counting the conditionally formatted cell works great - but the popup is NOT what i need - i want the count to post to a Cell. is that possible without having to do it manually myself? in other words - if 6x in a my row/range - the "condition is met" (green) - i want 6 to appear in a summary table cell below. Then i want to do that again for a different range/row (different team member) and so on. Thoughts?

    1. Hi! This is possible by changing the macro code and specifying the cells to which the results will be written.

      1. Hello! This is very helpful, thank you! I also have a question about editing the macro code. I am not a coder, can you please explain where (and how) in the conditional formatting macro code we would need to edit to output the count into a cell and not a popup box? Thank you!

  8. How do I adjust either the formula or the module to allow me to count cells by color in multiple ranges? Is it possible to add multiple ranges in the current formula in some way or do you have to adjust it somehow? For instance;

    =CountCellsByColor((B5:B12,E5:E12),AM5)
    Where B5:B12 and E5:E12 are the two ranges I want to count cell in by color to get the sum of cells with the color (ignoring any occurance of the color in columns C-D)
    And AM5 is the cell containing the color I want to count in the two ranges.

    1. Hi! I can recommend you create a separate formula for each range of data. Then summarize these results.

      =CountCellsByColor(B5:B12,AM5) + CountCellsByColor(E5:E12,AM5)

  9. if I have the same cell layout on several tabs within the same workbook, how could I count the number of colored cells in the work book on Cell A6 for example?

    1 workbook, 100 sheets.

    Count how many each of the cells are colored in the same cell.

    sheet1 A5
    Sheet2 A5
    sheet 3 A5
    etc

    have 2 colored cells.

    Sheet1 C50
    Sheet2 C50
    Sheet3 C50
    etc
    have 50 colored cells.

  10. Hi,

    The SumCellsByColor worked great in a local instance of Excel on my desktop, but when I move the script to Microsoft365 Excel, I'm getting 125 errors. I'm honestly not sure where to begin.

    Examples of errors:
    Line #2: ';' expected.
    Function SumCellsByColor(data_range As Range, cell_color As Range

    Line #2: ',' expected.
    Function SumCellsByColor(data_range As Range, cell_color As Range

    Line #2: ',' expected.
    Function SumCellsByColor(data_range As Range, cell_color As Range

    Line #2: ',' expected.
    Function SumCellsByColor(data_range As Range, cell_color As Range

    1. Hi! Check which characters you are using as delimiters. Download the sample file linked at the end of this article. You can use this file or copy the VBA code from it.

  11. Hi,
    I am trying to count 6 different cell colors into 1 number (red, yellow, pink, blue, green,purple, orange=7 ). Can I assign multiple values to get the desired results? Also is there a way that each counted cell is counted as .5? I'm trying to track supervision on each staff by 6 different supervisors and each colored cell represents 1/2 hour.

    1. Hello! You can use a separate CountCellsByColor function for each color and then sum the 7 results. If necessary, you can multiply the result of the CountCellsByColor function by 0.5

      1. Thank you!

  12. I need help with a formula that I'm needing for a new work spreadsheet that I'm trying to create to keep track of which employees have completed their yearly certifications. I have employee names in cells the first 25 cells of columns B through W (they are broken down in a specifc way so I can't add or combine more in a column). As each employee Right now I am using a CountA formula that I'm using showing the total of employees (= counta (B2:W25) but as I highlight the employee names when I get their confirmations I want that cell subtracted from the total so I can see at a glance how many employees are left. I read about the count by cell color formula and that would work but I can't figure out how to combine the two to get a total. In my mine I thought I could put =counta(b2:w25)-CountCellsByColor(b2:w25) and that would give me my total but it doesn't. Please help.

    Thanks in advance for any and all help!

    P.S. I have Ablebits downloaded on both my personal and work computers and it's helped me so much. Glad I downloaded and bought it.

    1. Hello! In the CountCellsByColor function, specify the second argument – Cell_color. This is a reference to the cell with the target fill color.
      For example,

      =COUNTA(B2:W25)-CountCellsByColor(B2:W25,A1)

      Hope this is what you need.

  13. Countcellsbycolor worked perfectly thanks very much. Is there a way to modify the code so it counts the number of rows of a colour within a certain range? I.e. all rows from A10:A50 that are coloured the same as B2.

    1. Hi! The first argument of the CountCellsByColor custom function is the range in which you want to count cells.

  14. How to get the totals updated when a color is changed?

  15. This is very helpful!

    Is there a way to combine CountCellsByColor and CountCellsByFontColor? I have a set of data that has 5 different colors and 2 different font colors and trying to get 10 unique values from it. I was thinking it would incorporate CountIfs but my tests haven't been working.

    Thanks in advance!

    1. Hi! Unfortunately, it is not possible to combine the two custom functions. A new custom function must be created.

  16. Hi, great extention and very helpful, thanks. But if I need to pick just several cells instead of a certain range I can't understand how to.
    for example instead of
    =SumCellsByColor(D18:F21;O20)
    I need to summm only four cells, like
    =SumCellsByColor(E18+F19+E20+D19;O20)
    but it doesn't work. What am I doing wrong?

    1. Hi! Use either a single cell or a range of cells in the SumCellsByColor function. For example,

      SumCellsByColor(E18)+SumCellsByColor(F19)

  17. Hello , I am using MICROSOFT OFFICE LTSC PROFESSIONAL PLUS 2021 and I follow the same formula and it still not work #NAME,
    Then I download the excel sample and copied the entire data with the formula and the result is the same #NAME

    Any help?

    1. Hi Fernando,

      From the sample workbook, you need to copy not only the data/formulas but also the function's VBA code. Have you done that?

    2. Hello!
      Have you saved the file where you added the code as a Macro-Enabled Workbook (.xlsm)? If the issue remains unresolved, please try downloading our sample workbook provided at the end of this article, unblocking the macro (Microsoft automatically blocks macros in any files downloaded from the internet), and see if the function works in that particular workbook.

  18. I have the following problem with respect to "Count on conditionally formatted cells"
    1. For my data I need to filter it first on 1st column lets say with INDEX or FILTER functions
    2. Range received as output of Step 1 needs to be used as Input of "Count on conditionally formatted cells"

    How to do this ?

    Example : lets take the same sample table as you showed but add another column before the 1st one as category
    Say choose 2 categories as "Fruit1" and "Fruit2" and place these category against each of 2nd column data

    Now I need separate "Count on conditionally formatted cells" for "Fruit1" category and separate for "Fruit2" category ...

    Pls help on how to achieve this ...

      1. hmm... I am not familiar with VBA.. Is someone could help with this ?

  19. Hey all! Thanks so much for posting this code. Is it possible to output conditionally formatted color count to a cell so that I can sum cells? I was able to use the macro to do it perfectly, but I need to be able to separate counts and then sum for a total. Is there any way to do this?

    1. Nevermind, I found the answer below from Alexander and have used this successfully. Thank you Ablebits team for this help!!!

  20. I can't get this to work to save my life. I'm trying to get it to count cells by color, used your formula and it if throws #NAME? in the place of "CountCellsByColor".

    1. Hi Steven,

      Did you save the file where you've added the code as a Macro-Enabled Workbook (.xlsm)?

      If the error persists, please try to download our sample workbook at the end of this article, unblock the macro (by default Microsoft blocks macros in all files downloaded from the internet), and see if the function works in that workbook.

      1. Same problem. What is need to be followed in the 1st paragraph?

          1. done this but still getting the #name? error

            1. Hi!
              #NAME? error means that either you got the function name wrong, or the function is not present in your version of Excel.

              1. Having the same issue. Copied and pasted the CountCellsByColor VBA code. Saved workbook as .xlsm. Still getting the "#NAME?" error when trying to use the function.

  21. Hello All,

    it was worked if count by number but not if it count by date. could you please help me on this.

    appreciated for your support.

  22. Hello. Thanks a lot for the codes. I am wondering whether it could be possible to use the function for conditionally formatted cells in a cell as a general function to be inserted in whatever cell (not as a macro in a dialog box). I.e. I am looking for adjusting the function for summing colored cells which would work for conditionally formatted cells as well. Any ideas about adjusting the code?
    Thanks.
    Stanley S

      1. Hi Alexander! Below, in January Svetlana Cheusheva wrote a reply that there is only a macro available for both sum&count. But directly under this comment I am writing now I already asked you about some possible way of adjusting your code for function from "count" to "sum". Is it possible?
        Thanks!
        Have a nice day,
        Stanley

        1. Hello!
          Try this code:

          Public Function SumByColorCells(CellRange As Range, TargetCell As Range)

          Dim TargetColor As Long, SumCells As Long, C As Range

          TargetColor = Evaluate("cfcolour(" & TargetCell.Address & ")")
          For Each C In CellRange
          If Evaluate("Cfcolour(" & C.Address & ")") = TargetColor Then SumCells = C + SumCells
          Next C
          SumByColorCells = SumCells
          End Function

  23. SIR,

    I AM NOT GET COUNT OF COLORS WHERE CONDITIONAL FORMATTING APPLIED BY ABOVE MENTIONED SOLUTIONS/

    KIDNLY HELP

    1. Hello!
      This custom function counts all cells shaded by a certain color. Including with conditional formatting.

      Public Function CountByColorCells(CellRange As Range, TargetCell As Range)
      Dim TargetColor As Long, Count As Long, C As Range
      TargetColor = Evaluate("cfcolour(" & TargetCell.Address & ")")
      For Each C In CellRange
      If Evaluate("Cfcolour(" & C.Address & ")") = TargetColor Then Count = Count + 1
      Next C
      CountByColorCells = Count
      End Function

      Function CFColour(Cl As Range) As Double
      CFColour = Cl.DisplayFormat.Interior.Color
      End Function

      1. Hi Alexander!
        Thank you for solving the case. Do you have any ideas on summing the range of cells including conditional formatted cells? Replacing "count" with "sum" in your code simply does not help but I do not know why...
        Thank you!
        Stanley S.

  24. Nice code.

    Appears to be a small typo

    VBA macro to count and some conditionally formatted cells.
    Sub SumCountByConditionalFormat()

    ... and SUM conditionally ...

    1. Hi Ron,

      Oops, a silly typo indeed. Fixed, thank you for pointing that out!

  25. Your code for "How to sum by color in Excel" is excellent with only one issue, it doesn't automatically recalculate if you color the cells after setting the the calcuation cell. Forcing the recalculate works well and my sheet is set to automatic recalculation but it will not. I assume closing and re-opening will also cause the recalc.

    Any help appreciated.

  26. I really like the function. I am wondering If there is a way to make it refresh on color change. in other words, I use it to track tasks. So when I complete the task change the color of the cell. If I use the paintbrush it works but if I just apply the new color it does not update until I click into the cell and click out.

  27. After add this module in excel, it need to loading everytime when I edit the excel, may I know how to solve this problem?

    2. It also is no responded and blur screen loading. Is the function not suitable for my excel or did I set sth wrong?

  28. I added in your VBA for Count by color across entire workbook and saved it as a macro enabled excel file, i created a new tab and filled a cell with the colour i wanted to contain cell A1 i then used the formula =WbkCountByColor(A1) in the adjacent cell. the following error was then generated: Microsoft visual basic Compile error: Sub or function not defined. Seems to relate to text in the code CountCellsbyColor? not sure how to remedy the situation?

    Thanks

    1. Hi!
      For this function to work, the code for the CountCellsByColor and SumCellsByColor functions must also be added to Excel. Read the Note carefully after the function code.

      1. Thanks, I got it to work and finished the workbook, however after closing and reopening the file in order to get the function to work it now reads VBAProject.Module2.CountCellsByColor(b2:b8, b2)
        Any idea why this has changed as now the formulas don't work and I'll manually have to edit them all to get the functionality back?

  29. Is there a function for the macro "Count and sum conditionally formatted cells" ?

    I need the number provided as an output in a cell that I can leverage with other formulas elsewhere.

    1. Hi Eric,

      Nope. For conditionally formatted cells, we only have a macro.

  30. I can get it to work when I place the macro in the specific workbook I am working on, but cannot get it to work when I save it in the Personal WB via VBA. Any thoughts? I create new WB's from a program each week and need it to work from the personal macro wb vs having to re-add it every week to the new wb. At that point it it not useful. :(

  31. hi,

    I have used your code in desktop successfully but when I upload into the server it shows " Security Risk - Microsoft has blocked marcos from running because the source of this file untrusted"

    Kindly assists with thanks.

      1. click file> and next move to option > trust option > trust settings> macro setting

  32. I'm using =CountCellsByColor(C4:AG4,AK3) and it did work well when I have applied all to spread sheet,but after reopening Excel workbook I got everywhere NAME ERROR. What I'm doing wrong?

  33. I also tried this, but it didn't work.
    =SUMPRODUCT(IFERROR(--(MOD($D$39:$L$47,1)=0),0),SumCellsByColor($D$39:$L$47,D9))

    Where D9 is the BASE color and
    $D$39:$L$47 is the range with different colors, integers, and decimals, which I am trying to SUM only the integers of the D9 color.

    Thank you very much for your time
    Best regards
    Drago

      1. Thank you very much for your quick response.

        Unfortunately, I get a #Value error. I copy and paste your solution, then try ctrl+shift+enter to activate it, but get a #value error.

        But we managed to do it by adding 2 lines in the macro code for the SumCellsByColor

        If IsNumeric(cellCurrent) Then

        If Int(cellCurrent) = cellCurrent Then

        Thank you so much for this article. It will save me a lot of time in the future.

        Best regards
        Drago

        p.s If someone is interested in the whole macro for this particular case, I could paste the whole code in the comment section.

  34. Is there any way to make the cells auto-calculate when applicable cells are formatted?

    1. Hello!
      Formulas are automatically recalculated when the cell value changes, but not the format. Your problem can be solved with a VBA macro.

  35. I have a table that lists all shipments not yet invoiced. I have colored all the rows using conditional formatting. As an example, shipments that are overdue for invoicing are colored red, based on more than 5 days past the arrival date of the vessel. We have several managers responsible for different types of commodities, e.g. bulk oils and petroleum, frozen foods, chemicals, etc.
    Can the CountIfs count how many reds, blues, greens, and yellows by manager? Do you have a code that can do this for me? At the moment I am counting manually.
    I have tried =CountCellsByColor(DBN!$B$6:$B$1299,SUMMARY!$C$3) but this results in total blue cells, not how many blue cells a particular manager has.

      1. Is there any VBA code for counting colored celled which are conditionally formatted?? i did not find solution anywhere for latest excel versions

  36. LOVE this code - thanks so much!

    In my sheet I have dates in Row 7
    And data in the rows under it

    I have a start date in Cell E4, and a end date in column d starting at D8

    I want to only sumbycolour when the dates are equal to or inbetween the 2 specified dates

    So Start Date is 12/11/21
    End Date is 16/11/21

    The row with the data i'm counting has data in it for the whole year

    I feel like I need to include an IF statement - but getting lost - appreciate anyones help!

      1. Thanks that helped heaps.... so I now have the SUMIFS working, and have it written like this:

        =SUMIFS(F5:Z5,F4:Z4,">="&B1,F4:Z4,"<="&C5)

        Where I have dates in B1 and in C5

        Is there a way to incorporate this with the sumbycolor - maybe a sumbycolorifs?

        I want the above to only sum when cells are of a specified colour.

        Thanks again

        1. Hello!
          You want to sum by both date and cell color. But these two conditions can be applied using different functions. Unfortunately, you can only combine them if you add VBA code to the SUMBYCOLOR function.
          You can filter by dates on your table and then use the SUM BY COLOR tool. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

          1. Thanks again - unfortunately due to other restraints in the workbook / I don't have the ability to use filtering for this.

            Anyone have the knowledge / skill required to write the code required to make sum by date and cell colour work together? Would so appreciate it!

            1. Hi @Alexander Trifuntov - any chance you can help with the code for this?

              1. Hi!
                Unfortunately, we are not in the business of writing VBA code. However, the filter is a basic Excel tool. I don't really understand why you can't use it.

  37. Hi
    I tried to use the function but every time I am getting #name error. What should I do?

  38. I am trying to collect data that is in certain color boxes on my spreadsheet to total up amounts of hours someone has done over a certain amount of time. I am pulling my hair out trying to find a solution. Can you please help before i go bald lol.

    1. My spreadsheet is a register so i need to add up different color boxes. I have 6 different colors for the aims that i need to add up. So with one color (blue) it is looking for how many hours that have completed over a 43 week programme. There is two things that i want to do. I want to add the amount of blue boxes there are but i also need to know the total of hours they have completed over this time.

  39. I tried the countcellsbycolor formula, it just plain did not work! i followed all instructions right & got the #NAME? dumb error over 7 over again.

    1. I got the same result, pressed F9, and it worked.

  40. " Ageing Cat
    Demand Date " 1-Nov to 10- Nov 11-Nov to 20-Nov 21-Nov to 30-Nov
    91 - 180 Days 1,00,000 20,000 25,000
    181 - 270 Days 25,000 5,000 10,000
    271 - 365 Days 1,00,000 20,000 25,000
    More than 365 Days 25,000 5,000 10,000

    1. In excel AA Column as a cell with color and another AS column weeks

      how to sum the amount with a color receive again which week.

  41. The VBA works great, thank you for posting. How do I incorporate the GetCellColor to count cells between a date range using a =CountIfs function? I have done the following so far but its returning a zero value:

    =COUNTIFS(E7:NE7, GetCellColor(NK4),E2:NE2,">="&E2,E2:NE2,"<="&TODAY())

    **E7:NE7 Data Range
    **NK4 cell color reference
    **E2 Start Date
    **NE2 End Date

    Appreciate the feedback. Thanks.

    1. Hello Jonathan!
      I cannot validate the formula on your data. But something like this will work for you.
      Please copy the VBA code to your workbook and then enter the following array formula (remember to press Ctrl + Shift + Enter to complete it):

      =SUM((GetCellColor(E7:NE7)=GetCellColor($NK$4)) * (E2:NE2>=$E$2) * (E2:NE2<=TODAY()))

      Hope this is what you need.

  42. When I close and open the worksheet again, It doesn't work (#Name?)

    1. Have the same issue!
      Can anybody help with this?

  43. Thanks for the coding - cut my work effort in half! Is there any way that this can be implimented into a COUNTIF/COUNTIFS formula - Ive tried majority of things and im not getting any results.
    The plan is that I would like to find a number based on how often one of my numbers has been highlighted through a table of alot of other numbers. I.e - say that A1 has the number in highlighted but also further along the table there is another one highlighted at J16 i would like to get a result of that.
    ive tried
    =COUNTIFS(Sheet1!U36:ZZ50,"15",Sheet1!U36:ZZ50,CountCellsByColor(Sheet1!U36:ZZ50,Sheet1!A16))
    where am i going wrong? thankyou in advance!

    1. Following this it would probably benefit to know my motive - i would like to know the number of time A21 (a number) comes up in the table d36:zz50 and has been marked yellow.

      1. I need something similar did you get an answer.....?

  44. Dear Concern
    How to used your formula with one more condition suppose i have different product in one column and there cashflow in second column and if the cashflow are highlighted by different colour as deffer and advance payment now i want for a particular product what is advance and what is deferred from huge data i cant get the required result from your formulas

    1. I am looking for a similar forumula - trying to use SUMIFS with the countcellsbycolour - did you find a solution?

  45. I have downloaded and used the Ablebits Add-on successfully, Thank you for that. What I would like to do with that data is have it show up in a cell and be update-able. I have a spreadsheet that I am trying to count the colored cells that are out of range, I don't need an average of the cells just the number of red colored cells. I want that data to then be documented in a separate cell not just in a report on the side bar. My scenario is as follows: F5:AM5 (Conditionally Formatted to turn red if out of spec) & AO5 (Count Amount of conditionally formatted red cells out of spec)

    1. Hello, Chad,
      Thank you for contacting us and for your interest in our software.

      If you mean our Count and Sum by Color tool, yes, it can count cells by font or background color, but, unfortunately, it is not possible to automatically update the result of calculation when the number of colored cells change in the current version of the add-in.

  46. Hello l have vertical blank spaces say 15
    Then say a color (say red) that would end the blanks being counted
    I then have more vertical blank spaces say 3 (3 blank spaces) in the same vertical line or drop
    And then a color (say red again) to end the count and it continues
    Then more blanks then ended with a red (blanks counted and ended count with another red)
    Etc, etc, etc all different
    I would like to sum up the blanks then number them before it moves onto the next lot of blank cells and number them also on the last blank space (or the beginning) of each lot of blank cell
    In other words when it gets counted down (vertically) to the last blank cell before the color in a cell it will sum it up.
    Then when I have a list of multiple blank results of the blank cells in block l want to work out the average blanks of all of them.

    In other words sum up the blanks that are broken up by the reds (it could be a different colour)

    Thank you

    Slane

    1. Hello, Slane,

      Our technical specialist took a look at your task. Unfortunately, there is no easy way to solve it. Most likely you need a special macro. I am really sorry we can’t help you with this.

      You may try to find the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry for not being able to help you better at the moment.

  47. Its really good but i wants to know if i change the colour then it can do auto change in sum

    1. Hello, Rikin,

      Please note that the sum and count of the colored cells won't get recalculated automatically. Once you've made all the necessary changes, simply place the cursor to any cell and press F2 and Enter, the sum and count will get updated.

      1. Hi,
        Is there a way to set up sumcellsbycolour so it calculates automatically? I have calculation options set up as automatic but this particular formula is not updating automatically. this formula works great otherwise.
        thank you
        Fiona

        1. the closest thing I can suggest is to have VBA code that updates on selection change, more on that here https://trumpexcel.com/vba-events/#Worksheet-Change-Event

          However, you have to move click the cursor somewhere before excel will recalculate

  48. I'm getting a syntax error on "Dim indRefColor As Long".

    1. As do I.
      "Compile error:
      Syntax error"
      I copy-pasted the code in to my own document and I still get that error. Doesn't get it with the downloaded example document, and can't figure out what the difference is!

      1. Hi Christopher / Anton, I believe the code in this page had some syntax issues. Please download the sample document from this page. Go to VBA module and copy-paste the code to your sheet.

  49. Hi,

    Thank you for the codes.
    I have used the code for the count by conditionally formatted color, and it works. But rather than the information being shown in a message box, I want the information to appear on a cell. Is this possible? Can I amend the code in a way to accommodate this?

    1. How were you able to count by conditionally formatted colour cells? I've been struggling with me returns!

      1. Hello, Khalid.

        It looks like our Count and Sum by Color add-in can help you with your task. The tool can help you sum and counts cells by font or background color in a few clicks. If you want to see how the Count and Sum by Color add-in works, feel free to install the fully functional 14-day trial version of Ultimate Suite that contains all our products for Excel (70+ add-ins) using this direct download link.

        After installation you can find the add-in in the Calculate group under the Ablebits Tools tab. If you need the instructions how to use the add-in, you can find them on its help page.

  50. functions are not working on conditional formation

    1. I am having the same issue

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)