+ Reply to Thread
Results 1 to 11 of 11

Determining frequency of particular cell color across multiple documents

  1. #1
    Registered User
    Join Date
    02-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    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. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    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. #3
    Registered User
    Join Date
    02-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Determining frequency of particular cell color across multiple documents

    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!

  4. #4
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    393

    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. #5
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    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. #6
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    393

    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.
    Last edited by mgs73; 01-14-2014 at 06:41 PM.

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    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. #8
    Registered User
    Join Date
    02-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    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. #9
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    393

    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. #10
    Registered User
    Join Date
    02-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Determining frequency of particular cell color across multiple documents

    Works splendidly.

    Thank you for the assistance!

  11. #11
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Determining frequency of particular cell color across multiple documents

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. determining multiple cell contents and proving a count
    By m50b25 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-30-2013, 04:24 AM
  2. Replies: 4
    Last Post: 05-06-2011, 02:02 PM
  3. Replies: 6
    Last Post: 04-07-2011, 01:07 PM
  4. Determining duplicate entries and frequency
    By Tissy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-20-2009, 10:08 PM
  5. Determining a cells background color using a formula
    By scottchampion@gmail.com in forum Excel General
    Replies: 1
    Last Post: 04-20-2006, 02:30 PM

Bookmarks

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