How do I set a Target Range

[Mike54]

I found this code on the web and have slightly modified it, it works okay but it works everywhere.

How would I restrict it's operation to a specific range i.e ("E10:F100")

I have tried various IF statements without success, I have tried setting a Range called "Staff" and tried to use Target but I'm getting nowhere, any suggestion please, Many thanks Mike.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next

Static OldRange As Range
Target.Interior.Color = RGB(216, 216, 235)
OldRange.Interior.ColorIndex = xlColorIndexNone
Set OldRange = Target
End Sub

 

[Marcelo Branco]

Does this do what you want?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static OldRange As Range

    If Not Intersect(Target, Range("E10:F100")) Is Nothing Then
        Target.Interior.Color = RGB(216, 216, 235)
        If Not OldRange Is Nothing Then OldRange.Interior.ColorIndex = xlColorIndexNone
        Set OldRange = Target
    End If
    
End Sub

Hope this helps

M.

 

[RoryA]

You can do this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next

Static OldRange As Range
If not intersect(target, range("e10:f100")) is nothing then
Target.Interior.Color = RGB(216, 216, 235)
OldRange.Interior.ColorIndex = xlColorIndexNone
Set OldRange = Target
End if
End Sub

 

[L. Howard]

Try this.

The RGB doesn't seem to work for me, so I used .Interior.ColorIndex

Regards,
Howard

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 On Error Resume Next
 Dim MyRng As Range
 
Set MyRng = Application.Intersect(Range("E10:F100"), Target)
  If Not MyRng Is Nothing Then
    Static OldRange As Range
    'Target.Interior.Color = RGB(216, 216, 235)
    Target.Interior.ColorIndex = 3
    OldRange.Interior.ColorIndex = xlColorIndexNone
    Set OldRange = Target
 End If
 End Sub

 

[Mike54]

Wow! Thanks gents that perfect, I must say that the line;

If not intersect(target, range("e10:f100")) is nothing then

Is clearly not the most intuitive phrase but yeah I think I follow that now.

Many thanks

Mike