I need to set the backfill colour of rows in excel based on the status of an order stored in a sql database. There may be 40 to 60 rows in the excel sheet. It will vary. These order names and status are stored in a sql database. The orders may be in the excel sheet even though they may not yet be in the sql database.
I basically need to run a query every 30 seconds or so and cross reference all the order numbers present in the orders column in the excel sheet and then change the backfill color of the rows based on the results, e.g white if not found, red if complete, green if running, etc.
The excel sheet will change every few days as the orders get updated. Is there a god way of automating this process to take account of the excel sheets changing or should I just make a template and copy the excel data into my template as the excel data gets updated. The excel data is changed by saving a new file with todays date. The old files are just deleted every few days or so.
Can somebody tell me where is a good starting point to achieve this. I have little experience in sql databases and sql queries from excel.
Thanks for your help.
I think you have less need of any SQL power techniques than you do the "Conditional Formatting" feature to be honest. With conditional formatting you can highlight cells where that or another cell meets a certain condition.
What version of Excel are you using, and can you post a copy of a sample worksheet so we can teach you how to apply them?
Ken Puls, FCPA, FCMA, MS MVP (Excel)
Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.