Excel- protected sheets and conditional formatting

[Pandamonium]

I just started a spreadsheet to track my credit card bills. Columns include due date, statement balance, pending balance, cashback balance, cashback details, the last month I used the card, the date I checked this data, and some miscellaneous notes.

I set up conditional formatting to highlight/bold cards that have not been used in the past 6 months, and any cards whose data I did not check in the past two weeks. (Comparing the dates to Today()).

I wanted to have a history of past months expenses by using one sheet per month. I figured that protect sheet would prevent conditional formatting from changing, but I was wrong. So my June sheet is now fully highlighted, which makes it useless for historical purposes.

How can I stop conditional formatting from changing my old sheets?

 

[Binky]

You could make a set of macros pretty easily that turn on/off the formatting. One could be used to freeze the formatting by applying fixed formats and removing the conditional. The other could be used to do the reverse, remove any fixed formatting and apply the conditional.

 

[Pandamonium]

Any idea how to begin such a macro? I've never written one and google isn't giving me much guidance.

For now I manually applied formatting to a copy of the sheet and have that as my record. But there must be a better way of doing this...

 

[jersiq]

You should be able to paste over to Word, then paste it back into excel. Haven't tried this, but this should keep the formats without the conditions.

 

[stlcardinals]

If it's for historical data, why not just save that month's worksheet to a PDF to preserve the conditional formatting?

 

[PowerEngineer]

A couple of thoughts:

1. If you decide to write a macro, you'll want to assign background colors to the cells based on your data checking rather than turn on and off conditional formatting.
2. Instead of comparing the dates on each monthly sheet to TODAY(), why not compare the dates to a specially calculated date on each sheet. Not exactly sure what makes the most sense, but maybe something like: MIN(TODAY(),DATE(year, month+1, 1)-1) where month and year are specific to each sheet. The the conditional formatting for each past month will be driven by the last day of that month.

 

[Snapshot1]

I just started a spreadsheet to track my credit card bills. Columns include due date, statement balance, pending balance, cashback balance, cashback details, the last month I used the card, the date I checked this data, and some miscellaneous notes.

I set up conditional formatting to highlight/bold cards that have not been used in the past 6 months, and any cards whose data I did not check in the past two weeks. (Comparing the dates to Today()).

I wanted to have a history of past months expenses by using one sheet per month. I figured that protect sheet would prevent conditional formatting from changing, but I was wrong. So my June sheet is now fully highlighted, which makes it useless for historical purposes.

How can I stop conditional formatting from changing my old sheets?

If what you actually want is to format when the value of a certain cell is within the last two weeks, you could use cell value between TODAY() and TODAY()-14. This way after the date is more than two weeks old the formatting will not be applied.

Snapshot