# Determining frequency of particular cell color across multiple documents

1. ## Determining frequency of particular cell color across multiple documents

Hi,

I am fairly new to VBA/Macros, but have some experience in Excel. I am looking for help with a particular analysis I am looking to perform across approximately 60 different documents, each with 5 sheets.

1. I want to only pull information from the second sheet of each document (the other sheets are redundant with other documents).

2. I have a list of projects in Column A and colored cells in Column B (green, yellow, and red).
I want to count the number of times a particular project (defined by a unique string in Column A) is a particular color (really I only need to determine red) across the 60 documents.

3. Within a particular document, I have been able to figure out how to transform a cell's color into a number in the adjacent column by defining a name: "color" as '=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,-1))' and inputting '=color' into the adjacent column. I am not sure how this can be applied across documents in an easy manner.

Basically I am looking for help performing the analysis described, and would appreciate any guidance or targeted resources that will help me get there.

I have attached a sample document to show what I am dealing with.SAMPLE.xlsm

Thanks!

2. ## Re: Determining frequency of particular cell color across multiple documents

Since you have a developed method for determining the "color", it should be pretty straightforward to open all the documents in a specific location, install the function, apply the formula on the sheet/range desired, then collect the results into separate workbook.

Please provide the step by step instructions for doing it one time completely for one workbook, including where it's located, sheet name, range, etc. as well as what info is then collected and placed where.

3. ## Re: Determining frequency of particular cell color across multiple documents

1. All files are located in a folder, let's call it C:\exceldocs

2. Open workbook1

3. Navigate to 2nd sheet (the name of the sheet varies)

4. Create name 'color', defined as =GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,-1))

5. Enter '=color' in Column M - all rows (rows 2-100 should be sufficient)

6. Open output_workbook

7. Go to Cell B2 and determine cell content

8. If B2 content (unique string) is in column B of sheet 2 of workbook1 AND the corresponding column M contains the number 38, then count in the output_workbook Column M

Not sure if this is clear or what you were looking for, but I appreciate the help.

Thanks!

4. ## Re: Determining frequency of particular cell color across multiple documents

The following should take care of points 1 and 2. I'm not quite sure what you want in point 3, but I think what I have done is what you want.

The code below opens every Excel file that is in the same directory as itself, then looks for pink cells (colorindex = 38) in column 2 of the second sheet. If it finds one, it either adds that Project to the list (if it's not already there) or adds 1 to the count (if it is already there).

``Please Login or Register  to view this content.``

5. ## Re: Determining frequency of particular cell color across multiple documents

You lost me at step 7. In step 6, wouldn't the macro actually be run FROM inside the output workbook, so it is drawing information to itself?

6. ## Re: Determining frequency of particular cell color across multiple documents

Steps 4 and 5 are not really needed, as the macros can work out the color of the cells without doing that. I've modified the code to take that bit out. I also needed to change what Row to use when reporting, as before it could potentially use the same row more than once, overwriting something important (oops...)

``Please Login or Register  to view this content.``

7. ## Re: Determining frequency of particular cell color across multiple documents

This is how I would add the named formula to the activeworkbook and enter the formula in a specific range:

``Please Login or Register  to view this content.``

8. ## Re: Determining frequency of particular cell color across multiple documents

Thank you both for the help.

JBeaucaire - I think since mgs73's method for determining the cell color works, I won't need to create the named formula within each workbook.

mgs73 - The macro you wrote seems to work in part, but it only captures the last entry for which the corresponding cell is pink. I have looked over the code - which I understand to an extent due to a basic knowledge of languages like Python and Javascript - and messed around with it a bit, but I can't figure out how to fix it. Basically it is writing over previous entries and also not counting up. Not sure if the findlastrow function is not working properly or if the "if,then" logic is incorrect as I am not 100% certain on the correct syntax. Any additional help you could offer would be appreciated.

9. ## Re: Determining frequency of particular cell color across multiple documents

My mistake... You are correct, there are errors in the FindLastRow function - typos, really (I typed it manually, as the code was on another computer so I couldn't copy/paste!)

I've fixed that, and also removed the test 'if r = 1', so it will always look for the LastRow. What I was trying to was account for these two scenarios 1) If there is nothing on the spreadsheet, the FindLastRow function returns 1 (as 0 would not be a valid Row). In this case, you would want to put data in that row. 2) If there is anything on the spreadsheet, you don't want to write data in the last row, you want to write in the row after.

What I did before does exactly what I was trying to avoid, but this should work now:
``Please Login or Register  to view this content.``
Here's the complete code - hope it works for you this time!
``Please Login or Register  to view this content.``

10. ## Re: Determining frequency of particular cell color across multiple documents

Works splendidly.

Thank you for the assistance!

11. ## Re: Determining frequency of particular cell color across multiple documents

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1