Hello there
The paste and paste special options are greyed out on a reasonably complex workbook which I am using.
I have managed to isolate the problem to the piece of code below. The code makes the colour of a selected cell change colour. can anyone explain why this code remove these options and can anyone suggest how to adjust the code so that the paste and paste special options are not greyed out.
thanks
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim nm As Name, rng As Range
On Error Resume Next
Set nm = ThisWorkbook.Names("CellAddress")
On Error GoTo 0
Set rng = Intersect(Target, Range("I15:I588"))
If Not nm Is Nothing Then nm.RefersToRange.Interior.Color = vbDarkBlue
If Not rng Is Nothing Then
If rng.Count = 1 Then
On Error Resume Next
nm.Delete
On Error GoTo 0
ThisWorkbook.Names.Add "CellAddress", rng
rng.Interior.Color = vbRed
End If
End If
End Sub
The paste and paste special options are greyed out on a reasonably complex workbook which I am using.
I have managed to isolate the problem to the piece of code below. The code makes the colour of a selected cell change colour. can anyone explain why this code remove these options and can anyone suggest how to adjust the code so that the paste and paste special options are not greyed out.
thanks
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim nm As Name, rng As Range
On Error Resume Next
Set nm = ThisWorkbook.Names("CellAddress")
On Error GoTo 0
Set rng = Intersect(Target, Range("I15:I588"))
If Not nm Is Nothing Then nm.RefersToRange.Interior.Color = vbDarkBlue
If Not rng Is Nothing Then
If rng.Count = 1 Then
On Error Resume Next
nm.Delete
On Error GoTo 0
ThisWorkbook.Names.Add "CellAddress", rng
rng.Interior.Color = vbRed
End If
End If
End Sub