Cell selection macro

[robertsmyth100]

Hi,

I am trying to write a code which changes the colour of a cell (A1) when a cell in a range (E14:E15)is selected:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Address = "$E$14:$E$15" Then
Range("A1").Select
With Selection.Interior
.ColorIndex = 0
.Pattern = xlSolid
End With
End If
End Sub

Could anyone help?

 

[VoG]

Try

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Intersect(Target, Range("E14:E15")) Is Nothing Then Exit Sub
With Range("A1").Interior
    .ColorIndex = xlNone
    .Pattern = xlSolid
End With
End Sub

 

[Andrew Poulsom]

Try:

If Not Application.Intersect(Target, Range("E14:E15")) Is Nothing Then

 

[robertsmyth100]

thankyou. How would I change the macro so that if another range, say D14:D15 is clicked then a different cell, A2, changes colour?

 

[VoG]

Try

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("E14:E15")) Is Nothing Then
    With Range("A1").Interior
        .ColorIndex = xlNone
        .Pattern = xlSolid
    End With
ElseIf Not Intersect(Target, Range("D14:D15")) Is Nothing Then
    With Range("A2").Interior
        .ColorIndex = xlNone
        .Pattern = xlSolid
    End With
End If
End Sub

 

[robertsmyth100]

thankyou.

One query - this code works in a new spreadsheet I created to test it but does not work on the spreadsheet I want to use it in. The spreadsheet I am trying to use it in already has macros that are working fine. Any idea why this might be?

 

[VoG]

You can only have one Worksheet_SelectionChange procedure per sheet. If you post your existing code we can probably combine them.

 

[robertsmyth100]

ignore that I was being an idiot