Need code to check if file exists then activate conditional macro

Hello,

I'm not sure how to pose this question because I don't know all of the proper terms / lingo, so I'll explain what Im trying to accomplish and cross my fingers someone can help.

I am making a "dashboard" for our work computers. Not a dashboard to display stats, but a dashboard to organize file locations and display status updates to the user. My team works at remote terminals and often switch around covering different locations (where the computers are located). Im trying to make it so that when a team member walks into the remote office, they can see at a glance if certain work has been done already or not. To that end Im trying to figure out a way to have a conditional format option change based on if a file with a certain date exists in a folder. EG a daily report is done at every location, every day. The file is named DailyReport_1.3.14.xlsm (the date changes for each day). What I want to do is make a VBA code that will check to see the current date, and then check to see if a file with a matching date is located in the reports folder. One of the problems I foresee is that the reports are organized by yearly/monthly folders. IE ...\2014\January\DailyReport_1.2.14.xlsm. I dont know enough about VBA to know if you can search multiple folders (2014 Jan - Dec & 2015 Jan - Dec etc).

My end goal is to have a little stoplight show red if that day's report has not been done, and show green if that day's report has been done.

What I started with is this code...

Please Login or Register  to view this content.

So I guess the long and short of it is that I need to determine the current date, check to see if any file matches that date, and then affect a conditional macro based on the answer. Is this possible? Please keep in mind that I really am a novice with VBA (I didn't know what it was until earlier tonight). I have a pretty good working knowledge of Excel.

Thanks so much in advance!

Moderator's note: I have added code tags for you because you are new. Please take the time to review our rules. There aren't many, and they are all important.. See #3. --6SJ

Try this code instead of you FilePath line:

Please Login or Register  to view this content.