Forum Discussion

Copper Contributor

Jan 10, 2018

Solved

Excel Conditional Formatting not functioning correctly after copying range to another sheet

Hi, I have the following situation: Sheet A has many cells with conditional formatting: > some of these cells use 'formula to determine which cells to format' > other cells use 'format onl...

excel

Macros and VBA

SergeiBaklan
Jan 10, 2018
Hi Ben,

In general yes, simple manual copy/paste (Ctrl+C -> Ctrl+V) transfers rules formula without referencing on source sheet. Try to test that with your data, if works you need to adjust your VBA code.

Ben Dossche

Copper Contributor

Jan 10, 2018

Hi Sergei,

Thank you for your reply.

If I copy the formula it does not work correctly.

However, by doing this I found out what happens:

the original conditional formatting formula in sheet A: "=ABS(S38/S39)>=Admin.TH.Person.high"

becomes this on sheet B:

"=ABS(A!S38/A!S39)>=Admin.TH.Person.high"

So: the formula in sheet B keeps refering to sheet A. That is not what I want... I want to have the cond. format formula unchanged, ie. refering to S38 and S39 on sheet B. Is that possible?

I overlooked this before.

SergeiBaklan

Diamond Contributor

Jan 10, 2018

Hi Ben,

In general yes, simple manual copy/paste (Ctrl+C -> Ctrl+V) transfers rules formula without referencing on source sheet. Try to test that with your data, if works you need to adjust your VBA code.