Conditional Formatting -- "Applied To" Keeps Changing

[JScotty317]

I've searched the stickies and FAQs and haven't found anything -- after 20+ hours of dedicated research I HAVE to ask you folks how to do this...

I have ~10 conditional formatting rules applied on a worksheet. Nine apply to Column H and the other to Columns A-N.

If I ever copy and paste ANY cell within rows A-N to another row, it updates the Conditional Formatting 'Applies to' to account for the cell moving.

Is there any way to lock this down so that the formula looks at the existing cell structure, not where the cell was when the rule was applied? It's ruining my spreadsheet's usability.

Thanks!
- Justin

 

[Chrisdontm]

When you paste it, you would need to paste as values only...

That way it won't bring over the conditional formatting...

 

[liveinhope]

start by asking you a question - "what are you hoping to accomplish by copy-and-paste a cell in columns A to N to another cell"?

If you are doing this because you want the pasted-to cell(s) don't have the same conditional formatting as the copied-from cells(s) but you would like them to then that's not the way to do it.

Instead do this home->conditional formatting ->manage rules-> show formatting for this worksheet . check the "applies to " range for each rule and modify as needed


If you're just wanting to copy the value and you don't want the pasted-to cell(s) t0 have the same conditional formatting then do as suggested by chrisdontm - use paste-special to paste value only, Then , if you need the paste-to cells have the same formulas do paste-special again and paste formulas (must do in this order or paste-special values will wipe out the formulas)

 

[hajiali]

I have had issues with this myself. Although still have not found solution. What I realize is when doing Copy and Paste Values only it does not transfer the Conditional Format as an alternate solution.