VBA macro to count cells with colour

Dear experts,

I need a simple macro which can count the number of cells with a given shade (other than the default shade in a range). The macro while running should ask for the range in a sheet, so that I could select it.

Thank you
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
you can use the countcolor user defined function from Microsoft

see attached.
ExcellearnerAuthor Commented:

thank you,

When i applied it in my sheet (file saved as xlsm), the result was #Name?

Can you please help me identify the issue.

Thank you
ProfessorJimJamMicrosoft Excel ExpertCommented:
you probably mistyped the function name.

check if you have put the spelling correctly it has to be exactly CountByColor  if it is mistyped it will give name error.

also make sure that this code is in your workbook module

Function CountByColor(CellColor As Range, CountRange As Range)
Dim ICol As Integer
Dim TCell As Range
ICol = CellColor.Interior.ColorIndex
For Each TCell In CountRange
    If ICol = TCell.Interior.ColorIndex Then
        CountByColor = CountByColor + 1
    End If
Next TCell
End Function

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
As ProfessorJimJam stated earlier that these are User Defined Functions. In other words a user defined function is a VBA code that can be used like an Excel's Inbuilt Function.

So if your workbook doesn't contain that user defined function and you try to use something like this....


That will produce an error like #NAME? because no such function exits in your workbook.

So in order to make it work for your own workbook, follow these steps....

1) Open the file uploaded by ProfessorJimJam.

2) Press Alt + F11 to open VBA Editor.

3) On VBA Editor, from project explorer from left side, double click Modules to expand it, then you will see Module1 otherwise you will see a + sign before Modules.

4) Double click the Module1, that will open the code window on the right side.

5) Click inside the code window somewhere and press Ctrl + A to select all.

6) Close this file.

7) Now open your original file and press Alt + F11 to open VBA Editor.

8) On VBA Editor, click on Insert and choose Module and this will open a blank code window.

9) Click on the blank code window and Ctrl + V to paste the copied code from the previous file.

10) Now you are ready to use those functions in your original workbook.

Does this help?

Edit: Don't forget to save your workbook as Macro-Enabled Workbook.
ExcellearnerAuthor Commented:

Thank you both,

i am unable to save the vba in the spreadsheet. Everytime I open the spreadsheet. it asks me to either to update or not to update. Either option returns #name?

I followed the process mentioned above by sktenner. But the still the problem exists.

After following sktneer's process, when I close what is the process to save the vba in the spreadsheet.

Secondly, why does the window for update or do not update appear as soon as i open the spreadhseet. Whereas this does not appear when i re-open professorjimjma's file.

Kindly help. It is urgent.

Thank you
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
1) Update and Don't Update warning you get only when your workbook has an open connection or workbook has a reference to an external link.
Visit the following link to know more about it....

2) After copying the code to your workbook, use SaveAs option or simply press F12 key to open SaveAs dialog box and from the Save as type drop down select Excel Macro-Enabled Workbook. So your original workbook will be saved as a macro workbook with the same name at the provided destination directory. Now you will have both the workbooks a normal .xlsx file and a .xlsm file with the same name. The macro file can be identified by the file icon with an yellow exclamation mark within it. Next time you will need to open the macro file not your previous normal file to use that UDF.

3) Once a UDF is present in your macro enabled file and you start typing the formula like =Count..
a list of all the function which starts from count will be popped up and you can see your UDF in that list also. And if you don't see it there that simply means either your macro workbook doesn't have that UDF in the Module or you have not opened the macro file or you have misstyped the name of the function.

Does this help?
ExcellearnerAuthor Commented:

The file I am referring to has already been saved as xlsm. After i included the vba, i once again saved the file. It now seems to have solved the issue.

Further, what is Funcres.xlam, this appears whenever i try to save. The files do not have any link to other files. The link is being created by the macro (Funcres.xlam).

Generally, how do i check if the above macro is still in the excel file if i were to open it after save and close.

Thank you
ExcellearnerAuthor Commented:

Thank you for the immediate response.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Funcres.xlam is nothing but an add-in and you see it if you have activated the Analysis TookPak.

Visit the following link to know more about it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.