Conditional Formatting multiple tables the same way - VBA

[choop]

Hello,

I have a spreadsheet with about 7 identical (same headings, columns) tables. Those 7 tables are lists of employees for each of our seven plants. In addition to employee names, it includes a bunch of detail about job, manager, etc.

I have about 8 or 10 conditional formattings that I would like to use, but i'm finding that Excel gets very confused when I copy and paste data between tabs, or add new rows.

I would like to have a macro that can cycle through each of the tabs, clear all conditional formatting, and then re-apply my pre-defined conditional formatting. The trick is that the number of rows isn't the same between the tables, so ranges aren't a good option, the code would really need to apply to the table.Column itself so it dynamically incorporates all data, while leaving blank rows as regular formatting.

Tables:

Plant1​

Plant2​

etc...​

Columns include:

Site​

Baseline Employee​

EE Number​

SEGMENT​

Role Origination Date​

IN WORKDAY?​

Manager Name​

Shift​

Workday Title​

Simplifed Business Title​

Title-Shift​

B/V/SGA​

Comment​

January​

February​

March​

April​

May​

June​

July​

August​

September​

October​

November​

December​

Is this possible?

thanks

 

[Fluff]

To get the whole table you can use

With ws.ListObjects(1)
   .Range.FormatConditions.Delete
   Sheet22.ListObjects(1).DataBodyRange.Copy
   .DataBodyRange.PasteSpecial xlPasteFormats
End With

But if you have CF rules, outside this range, they may still exists.