Automatically format recurring sheet

[troyd1]

I work with a sheet that is recreated on our server on an ongoing basis. I want to format it (row/column/cell coloring) based on the data in that sheet. What is the best way to accomplish this?

I am a seasoned excel used and also a programmer. I have done limited macro's and vbscripts, but am sure I can figure it out with some advice.

I am thinking I should either design a macro/vbscript to clear out the data in a static worksheet and then update the data with the formatting required.

Please advise on the best way to accomplish what I am trying to do.

Any suggestions would be greatly appreciated.

Thanks

 

[BenMiller]

You can create a template file (.xlt or .xltm) and the formatting can be conditional formatting in the template ... would that work?

 

[troyd1]

Not sure, please elaborate. The excel spreadsheet(workbook) will be overwritten everytime a process in ran. I need to either automatically format that workbook or import the data from that workbook sheet into another workbook sheet and format it there. I am thinking the later as I also have a second sheet that I need to vlookup from into the first sheet.

 

[BenMiller]

If you will be overwriting the data, you won't need a template. Are you familiar with conditional formatting?

 

[troyd1]

No, not really. I am not even sure of the best way to accomplish this. Let me lay out what I need to do with more specifics:
I have workbook thedata.xls that gets over written each time with variable data with the same columns, but a different number of rows. I need to do some formatting on it each time. In addition to the formatting, I have a second workbook, called lookups.xls that has a list of keys and dates in it. I need to end up with a formatted workbook either of thedata.xls, or a second workbook that has the data from thedata.xls in it. The final data will have a column that has a vlookup to the lookups.xls sheet displaying the date and also some formatting of the sheet based on the data (the conditional formatting). I know how to do the vlookups and can probably figure out the conditional formatting. What I am really looking for is the best way to either format the recurring sheet or a way to copy that data to another preformatted sheet probably using a macro or some vb code.

 

[troyd1]

I think I have a better understanding on how to approach this. I am going to create a new topic that is clearer for the parts I am not sure about. You comments help me to go in the right direction. Thanks.

 

[BenMiller]

Great, sorry I wasn't able to help you out all the way