Thread: Maintaining conditional format when condition no longer met

Hi,

I see from searching the forums that similar questions to this have been posted before, but can not find a satisfactory answer. Here's the problem: I have a large excel sheet that has conditional formatting for column heads that reference a condition at the top of the sheet..eg. if cell value (at top of sheet) is equal to "f", make column head red and bold. This spreadsheet gets split up in to mulitple smaller excel tables without the condition lines that are present in the large file. I want to maintain the conditional format in the smaller tables. I've tried copy pasting the conditional format and I've tried copy pasting format then removing the conditional format. Trouble is the formatting is still lost once the spreadsheet is divided up. Is there any solution to this problem?

Hi, and welcome to the forum.

You don't say whether you are copying the CF cells to other worksheets, but if so, as far as I'm aware you can't use references to other sheets (or workbooks) in CFs.

However you should be able to copy CFs within the same sheet. Just make sure you make the reference to the 'f' cell at the top of the sheet an absolute reference in the CF formula.

i.e. use

=$C$1="f'
rather than just
=C1="f"

HTH

the problem is that the large spreadsheet with the conditional format is chopped up into small tables, each of which does not have the original line on which the condition was based. I was hoping there was a way to copy/paste the format created in the original sheet by the conditional format without leaving it conditional. All paste special variations fail to carry over the conditonal format as does paste formats follwed by manual removal of the conditional format.

Hi,

Did you follow NBVCs advice about naming the cell that contains the 'f' reference, and using this name in the Cond. Format, 'Fomula Is' option?

The cell containing the CF should copy and paste to other worksheets as you require.

Rgds