Create a list of info from column/row if condition is met

Hi all,
My first time posting. I've been reading (and using) posts for a while, so thanks to all those who have gotten me this far.

So I'm trying to create a workbook that will track who has been trained. I have it close to set, but I can't figure out the 'Delinquent Training' tab. I want to create a list of everyone who is not trained and what they are not trained on. So on the 'Training Matrix' tab, if the person has a red cell (Conditional format with "•" (Alt+7) as the text based on cell formula) under their name, I want to pull the following info:

Document # - column B of that row
the Name of the person - the first row of that column
The department of that person - 2nd row of the column prior to training status
The date they were last trained (if any) - same row in the previous column

I've tried recording a macro, but my VBA skills aren't that great (just discovered it a week ago...)

Eventually, the list will have 200+ trainings and employees, but I want to work on the format before dumping all the data in there...


Training draft sandbox.xlsm

Hi QEJohn,

Welcome to the forum.

See the attached file.. and let me know if this helps.. thanks.
Training draft sandbox.xlsm

Regards,
DILIPandey

<click on below 'star' if this helps>

You are welcome QEJohn,

Cheers

Regards,
DILIPandey

<click on below 'star' if this helps>