This question is a clarified subset of Prevent breaking formulas/formatting by moving cells in locked worksheet, which hasn't been solved yet.

Imagine a simple Excel game:

    | A  | B |
 1  | 19 |   |
 2  |  5 |   |
 3  | 23 |   |
 4  |  8 |   |

The user has to go to the cells in B and retype in the number on the left (I know, stupid, but it's an example!). To make it visible if he/she did it right, there's a conditional formatting:

  • When =$A1<>$B1 is true, the text is getting red; applied to =$B$1:$B$4.

This works, but...

  1. The player types 8 into B1. It gets red. He/She realizes the mistake and moves the 8 to the correct cell with drag&drop.
  2. The 8 stays red and the conditional formatting is split up into

    • When =$A3<>$B3 is true, the text is getting red; applied to =$B$3:$B$4.
    • When =$A1<>$B2 is true, the text is getting red; applied to =$B$2.

How can one prevent breaking the formatting when using drag and drop?

  • 2
    It is not solved because it can't be done outside of vba that reinstates the conditional formatting every time the cell value changes. I would recommend training the user not to use drag and drop or copy and paste. That would have the same effect. The user can copy and paste special the values only and it will work. But this is a training issue or vba. – Scott Craner Jul 6 at 16:21
  • This would be a perfect answer. :) – Bowi Jul 9 at 7:12

Your Answer


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.