Retain clipboard during macro run

[Jubjab]

I have a workbook with a small macro routine on sheet1 that is run on a Worksheet_activate event. This clears the clipboard, meaning it is impossible to paste anything into the sheet.

Any ideas on how to make pasting possible?

PS. The macro needs to run as soon as the worksheet is activated, so a worksheet_change event is out of the question.

 

[Andrew Poulsom]

What is you macro doing that clears the clipboard?

 

[Jubjab]

What is you macro doing that clears the clipboard?

It colors a range of cells (creating a heat map). Here is the entire code:

Private Sub Worksheet_Activate()
            For Each c In Range("I5:K73,I78:K157,I162:K191,I193:K213,I215:K245,I249:K277")
                Select Case c.Value
                
                Case 0
                    c.Interior.ColorIndex = 10
                Case Is >= 100
                    c.Interior.ColorIndex = 52
                Case Is >= 24
                    c.Interior.ColorIndex = 12
                Case Is >= 12
                    c.Interior.ColorIndex = 43
                Case Is >= 9
                    c.Interior.ColorIndex = 6
                Case Is >= 6
                    c.Interior.ColorIndex = 36
                Case Is >= 5
                    c.Interior.ColorIndex = 9
                Case Is >= 4
                    c.Interior.ColorIndex = 7
                Case Is >= 3
                    c.Interior.ColorIndex = 38
                Case Is >= 2
                    c.Interior.ColorIndex = 35
                Case Is >= 1
                    c.Interior.ColorIndex = 4
                Case Is < 1
                    c.Interior.ColorIndex = 50
                Case Else
                    c.Interior.ColorIndex = 2
                End Select
            Next c
            
End Sub

 

[Andrew Poulsom]

Can't you use some other event, eg Change or Calculate, to trigger your formatting?

 

[Jubjab]

Can't you use some other event, eg Change or Calculate, to trigger your formatting?

Change doesn't work, but Calculate seems to do the trick. Had forgotten about that event. Thanks!