• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to find and copy text

Hi. I’m hoping to get some help with a macro. I made an Excel (2013) “training tracker” workbook that has multiple worksheets – one for each department (“Administration”, “Operations”, “Natural resources”, “Wildlife Center”, “Enterprises” and “”Education”). Each sheet is the same; with column A showing the names of the employees, and columns C through M showing different types of training. For each employee a date is entered for the various trainings. Conditional formatting is set so that the dates entered in the cells turn the cell a color (red, yellow, green) depending on how far out of date the training is.

Also in the workbook is an “Overdue” worksheet with three columns: A- labeled “name”, B – labeled “Training” and C – labeled “Date”.

I need a macro to scan all of the department worksheets looking for red cells. (The range of the search on each worksheet is C5 to L50).

Every time a red cell is found on one of the department sheets, I want to copy:

1) the name of the person in column A on that red cell’s line,

2) the text in row 4 of that red cell’s column,

3) the date found in the red cell.

These three pieces of information need to be pasted to the three columns on the “Overdue” worksheet – in columns a, b and c respectively. (Macro can overwrite old data - Starting with cell A6 each time it is run.)

Any help I get would be greatly appreciated! Thanks!

Dave

▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !
 
@Dave at the Park Interesting question. There is no simple way to read the cell's color when it is formatted to thru CF. But we can use autofilter to filter all the red cells (whether they are painted red manually or thru CF).

So I used that logic to filter each column and extract the necessary information. As you have not provided a sample file, I just made up a bunch of data that mimics your situation. Please find the attached workbook. You can find the macro findReds in module1. Make sure you clear any existing data (leave the titles) in overdue worksheet. Also, change the RGB code according to your own CF rules and the cell addresses.
 

Attachments

  • overdue-items.xlsm
    49.8 KB · Views: 10
Back
Top