Macro to apply formatting to an unknown range of rows & columns

Hi,

I hope you guys are able to help me with this. I have built a report using a combo of macros & formulas . Macros I've used import some data files & then save the report as values in exls format. The Register tab picks up values from a data sheet & performs calculations on some columns.I've attached a sample file to make things easier to understand.

The data tab contains the imported data (used on the 'Register' tab). These are used in Columns A,B,C,D,G,H,I,J Columns E&F contain formulas referencing sheet 'Report' . The last 4 columns Tk1...Tk4 on the Register tab, can have up to 5 more columns added , depending on the imported data.Also the no. of rows can change.

I need to be able to do the following:

1) Automatically apply our company's standard border formats to all data that is present on the register tab. for the header columns & the data below it.

2) The company banner (blue rectangle) & text box in it should extend out to all columns in case there is data in there.Eg, if the last column is N, then the banner should end further ahead.

3) 2 rows below the last row with data , should contain a total , which sums up values in column E- 'Votes'.

All of this seems simple but is complicated by the unknown rows & columns in the data. The formulas have to be dragged down upto 10000 rows to accomodate for possible data entries. I couldn't use last row or current region, as they count cells with formulas too, although there might not be values in them. I've been looking for an answer for the past 3 days with no luck.It doesn't help that my macro knowledge is pretty basic.Let me know if you guys have any suggestions.