Problem with Conditional Formats and Table Format in Excel 2k7

[Michael Kensy]

Dear all

I developed a quite complex (at least for me) Excel sheet to document, anlyse and control processes. Right now the Sheet reaches to column ABY and stretches over about 400 rows. We are planning to expand this even further Let me know if I should post a sample of the sheet structure here.

Due to the complexity it seems it is not possible to apply any table format. This might be because the table does have 3 to 4 header rows.
The reason why i would like to is, that over weeks we need to insert new rows at different ares of the sheet, not only at the bottom and I woule like formulas to be autofilled. That doesnt happen right now. A solution might be to limit the headers to one row only and to separate the other rows with an empty row which might be hide but may be there are other solutions.
The second problem is probably also related to inserting rows. I defined various Conditional Formats which applz to several separat areas. What I mean by this is that the Apply reference looks like A1:B400, D1:E400, G1:H400 and so on. During our work it happens again and again that these references get in a mess although my colleagues promise that they dont use the standard paste but Paste Values. If so why CFs get messed up?

Does anyone have any ideas regarding above two problems?
--
Regards Michael

 

[mole999]

Two things

the headers you use are they merged, in which case change each to centre across seelction

try changing your CF to A:B / D:E as complete columns (test on a a copy). They other way is to create code and remove / reapply formats every so often

 

[Michael Kensy]

Thank you Mole, for both explanations. Especially to apply the CF over entire columns seems promising.
--
Cheers Michael