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

[Ben Dossche]

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 only cells that contain'

Everything works fine, formatting changes according to changes in the data!

Now I copy a range of sheet A to sheet B using VBA, that works OK.

However: in sheet B the conditional formatting of these cells is not working correctly anymore:

> cells using  'format only cells that contain' work correct! formatting changes correctly when data changes

> cells using 'formula to determine which cells to format' do NOT work correctly: the formatting is not being changed when data changes....

Looking at the conditional formatting rules on sheet B: everything looks fine, rules are ok, cell references are OK, formulas OK.

I'm using Excel 2013 on Win 10.

Is there any solution for this problem??

Greetings

Ben

[#2]

Hi Ben,

If you copy the formula from the rule and use it somewhere in the cell of Sheet B does it work correctly?

[#3]

Copy and Paste into a conditionally formatted cell will delete the rules in the pasted cells.  If you just paste the values or formulas using Range.PasteSpecial xlPasteValues or Range.PasteSpecial xlPasteFormulas, the conditional formats should still work.  

[#4]

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.

[#5 · Accepted solution]

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.

[#6]

Thanks for your help! That solves my issue. I needed a small adjustment in my VBA copy code.

[#7]

@Ben Dossche 

Does it mean, I need to edit the Copy code written in VBA (If so can you give direction of how to do it ?)