For Each column in Table format cells
The attached Excel 2003 workbook contains 2 macros TblFmtGet and TblFmtSet, which can be run from the attached Tst1 toolbar. TblFmtGet works, but as explained below I'm having problems with TblFmtSet.
Sheet Original contains a formatted table (tbl_1Main).
I copied tbl_1Main from sheet Original to sheet Copy.
I want a macro to apply the formatting in Original to Copy: TblFmtGet outputs cell formatting info in Original.
I copied the cell formatting info produced by TblFmtGet from Original to Copy, then ran TblFmtSet, which is where I need the help.
The user would select (in this case) cell C23 of sheet Copy, then run TblFmtSet, with the aim of formatting the table using the formatting info below the table.
Q.1: I like the idea of using a “for each x in y” type syntax, but the current code with its offset to obtain the required info isn’t working. Is it doable?
Q.2: For horizontal and vertical alignment TblFmtGet returns the numeric values of the constants; however to apply formatting it seems I have to apply the constant (eg xlRight) not its numeric value (eg -4152). How do I get TblFmtSet to recognise the number provided by TblFmtGet, whether by lookup, conversion, or …?