Maintain formatting after refresh of data in Excel table via Power query

Unfortunately, I think you are correct in that you will have to continue your VBA solution. Perhaps using VBA to refresh the query and then, when that has been done, to restore your formatting.

And I did not fully understand your meaning when you wrote "formatting is linked to the column header." That is the case when dealing with tables generated by Power Query.

The font style, color, cell interior color, and the color and nature of the grid lines can be created with a Custom table style, and will be used when new columns are created. Unfortunately, it does not seem as if Conditional Formatting is a feature of the TableStyle object.