Paste disabled when using SelectionChange

[ChgsAllAround]

I am using the worksheet_selectionchange macro. I noticed that if you refer to any other cell than the one that is the "target" cell, the Paste option is greyed out. This means that for the person using the worksheet, paste is never available. Is there a way of getting around this? I've tried both direct code inside of the macro and calls to other subs--neither works. I'm guessing that it's related to the fact the the macro is essentially using the excel clipboard to keep track of the content of the target, but that's not very helpful to the end-user. Any suggestions? Thanks

 

[Jonmo1]

Put some code in your event that restricts it from doing anything outside your target range...

Like if you only want the event code to run if the target cell is within column C

Then make this the first line of your event code.

If Intersect(Target,Range("C:C")) Is Nothing Then Exit Sub

Now, the event code will only run if the user selects a cell in column C
that should help..

 

[ChgsAllAround]

Thank you. My problem is that I NEED to refer to cells other than the target in this macro. For example, Col A and Col B have dates. Col C is colored depending on how far apart the dates fall. So...the user changes a date, hits enter, and the macro activates to check for time span.

"WhichColor" is a custom function that uses a case statement to return the proper ColorIndex

For i = 1 to 5
Range("C" & i).Interior.ColorIndex = WhichColor(Range("A" & i).Value, Range("B" & i))
Next i

Any more ideas on how to reference other cells in this macro and still have paste available?

 

[Jonmo1]

It will still be able to "Refer" to other cells.

That code will just restrict the code to be TRIGGERED by column C being selected. so it doesn't run if OTHER cells are changed..

 

[VoG]

AFAIK the event code being called will clear the clipboard. One solution would be to paste the clipboard elsewhere (perhaps to a hidden sheet) then to grab it back again within the event procedure.