A real puzzler: Excel table resized by code looses 2 of 3 conditional format rules

[rdperkins]

I have created tables in a workbook which adjust in number of rows based on the number of months that need to be represented. The start and finish date are contained in cells and the adjustment macro runs from a command button using the code snippet below:

                            If ActiveSheet.ListObjects.Count > 0 Then
                                For Each oLst In ActiveSheet.ListObjects
                                    oLst.ListRows(oLst.ListRows.Count).Delete
                                Next oLst
                            End If

The code works great to resize the tables, but the tables loose all but one of the conditional formatting rules I set-up. This wouldn't happen with ranges, but I loose the capabilities to fairly easily resize the tables with code and have the formulas populate correctly.

I know there are some Excel Geniuses out there who can help me.

 

[Luke M]

How are the CF's defined? Do they apply to the entire table range, or specific rows? Can we write the code to add in the CF's?

 

[rdperkins]

I was answering your questions below when I thought of trying to delete the CF rules and recreating a single rule with just the Fill defined, avoiding the Number format tab. Hooray it works!

The CF is formula based, "=$A69=CalcDate" where A69 is the top-left cell in the table. The CFs, all based on the same formula, all relate to the same rows, but each of the three refer to a group of columns within the table. There are 3 contiguous non-overlapping ranges covered by the 3 CF rules, covering the entire table. The purpose of the CF formulas is to highlight the specific month-row based on the date entered in CalcDate which is controlled by a spinbutton. Works great except some of the columns are date formats, the others numeric with zero and 2 decimals. Works nice right after I create the CF rules, but as soon as I run the re-size routine, it dumps the last two CF rules and formats everything on the row as dates. I couldn't find a way to avoid doing a number format when I was doing the CF formatting.