Intersect, Target, Range - referencing from another cell, a problem....

Hi,

I have this private sub on a worksheet.

Please Login or Register  to view this content.

This works fine, copying the value of the cell selected in column C, and pasting it to the range U2 on 'Sheet1'.

However, I've now referenced the value of C5:C1000 from cells D5:D1000. So C5=D5, C6=D6 etc... This is causing the above code to not quite work. When i then click on a cell it copies the value of "V2" on Sheet1 into cell "U2" on Sheet1. I realize this is something to do with the fact that Im now referencing another cell (one across from the original), but i cant quite work out how to fix it.

Any ideas?


ps: I have to reference the cells, as another macro on another sheet copies and pastes information into the sheet with the code (ie into C5:C1000) As a result this automatically triggers the private sub - which i don't want it to do. So referencing it means the private sub doesn't get triggered when the other macro runs.

There is no code here that will copy column V. However you may keep your original code and simply add@

Please Login or Register  to view this content.

to the beginning of the other copy macro and add:

Please Login or Register  to view this content.

to the end. This will stop this SelectionChange event from running. (also, it is probably that your other code does not need to select anything which would also remove this problem )

Edit - typing a new explanation, please hold

I see - you have formulas in column C now? So you must use:

Please Login or Register  to view this content.

instead of this:

Please Login or Register  to view this content.