Forum Discussion

Ben Dossche's avatar
Ben Dossche
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 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

excel
Macros and VBA
  • SergeiBaklan's avatar
    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.

6 Replies

  • Mark Fitzgerald's avatar
    Mark Fitzgerald
    Iron Contributor
    Jan 10, 2018

    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.  

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 10, 2018

    Hi Ben,

     

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

    • Ben Dossche's avatar
      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's avatar
        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.

Resources