Prevent Excel from moving the conditional formatting formulae with the data

[timparr]

I have a workbook which has two copies of the same data (one in a hidden sheet). This is so that users can make changes to the data and it will track the changes. That works fine using an index formula but I also want to use conditional highlighting to show the cells which the user has changed.

If the user just types into the cells and deletes then all is fine (I have a separate conditional for each cell of the type =g3<>check!g3. This isn't a problem since the sheet is being generated automatically from my application.

If the user copies and pastes, or drags and drops the cell then the rule moves with the cell and it doesn't work properly.

Things I have tried:
=INDEX($1:$100,4,5)<>INDEX(Check!$1:$100,4,5)

This sort of works - it does highlight the place that the new data is dropped, but it doesn't highlight the 'gap' left behind (which is obviously also a change). My other formulae for getting the changes uses this method - Check!C4 = INDEX(Data!$1:100,3,4) which is fine because in that case the formula is on the check sheet which isn't being manipulated by the user.

I have also tried to access the undo list in vba to detect a copy/paste which is fine as far as it goes, but it doesn't seem to have any members to specify what was copied/pasted just the fact that such an event happened.

I did see a post from someone who said that 'locking the cells; fixed the problem but when he was asked for clarification did not respond.

I hope someone can help with this!

Thanks

Tim

 

[SpillerBD]

Conditional formatting is part of the Cell. Moving the Cell will always move its parts, including the conditional formatting.
You'll have to see if there is an available Protection for the Cells that provides the desired security without allowing the user to do what you want to allow. Perhaps all you need to do is protect the structure or the Workbook.

 

[timparr]

I did try a simple lock of the structure but no dice. I will play around with some of the other locks. I suppose another alternative would be to implement my own drag and drop or cut an paste. Is it possible to handle mouse clicks on cells? I could then have a left click 'pick up' a cell and a right click 'drop' that cell somewhere else and handle the move with VBA?

 

[Threazy]

I'm not sure this would work in your situation, but the through occurred to me to abandon conditional formatting altogether and create some kind of trigger (a button?) that launches code that does the formatting for you. I'm having the same problem - I really hate this "feature" of excel.

 

[FDibbins]

As was pointed out, you cannot spit a cell from it's formatting (or conditional formatting). Have you considered locking the cells so they cannot copy/paste or drag/drop?

 

[CatyH]

How do you prevent users from drag/drop? I have protected 90% of the sheet but have 10% as the place users enter data. If they drag/drop cells then all my referring formulas get messed up. Protecting the sheet seems to do nothing, and I've tried some VBA that I found on Mr. Excel but doesn't seem to be working either. Any help is GREATLY appreciated!