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!

Thanks in advance!

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!

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.

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.

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.

Works splendidly.

Thank you for the assistance!