(Excel 2007) I have conditional formatting that is based on an array, applied
to all cells in a worksheet. The formatting underlines every third row after
monthly totals by:
=MOD(ROW()-MAX(ROW($A$1:$A1)*($A$1:$A1="Totals for")*($A1<"Totals for")),3)=0
The words "Totals for" appear in col A, various rows. For test purposes I
have them in A41 and A78, and the word "Date" in A4, and the formatting shows
correctly
When I save the workbook and re-open it, the wrong rows are underlined.
If I go to the "Edit rule" screen and click OK without changing the rule,
then Apply, the rule works correctly until next time I close and re-open the
workbook, when rows 42 and 78 are underlined. Editing the rule (without
changing it) and applying gets the correct rows, 44 and 81, underlined, but
the result is lost on saving.
Using arrays like this worked fine in XL XP. It fails in XL 2007 in both
compatability mode for old XP workbooks and for new workbooks.
It makes no difference whether I am using Win XP or Vista, and fails on
every computer that I have access to.
Any ideas?