Worksheet_SelectionChange - So.... what am I doing wrong...

Hi all,

I would like to run a macro when cells are clicked. I've placed my code into the worksheet level, but I'm not getting any activity. I don't know if I need to "turn it on" somehow or what exactly...

Here's a couple versions of what I have, but in both cases when I switch back to the worksheet, Nothing happens. I'm trying to do a yes no questionnaire without buttons or data validation drop downs. You click the "Yes" cell, and it puts "Yes" in the reference column and changes the color of the clicked cell while "greying out" the "No" cell. And if you then go click on the "No" cell it reverses it.

Furthermore, I would like to know how to apply this across several ranges. I'm terrible with this sort of thing in VBA, but I could make a formula in no time.

The only difference between the versions is one calls another function, but I don't see why it can't just do the work at the event.

Ver 1

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Call SelectionActivity
End Sub
Private Sub SelectionActivity()
If Target.Address = "B2:B22" Then
        Range("B2:B22").Interior.Color = RGB(200, 160, 35)
        Range("D2:D22").Value = "Yes"
        Range("C2:C22").Font.Color = RGB(150, 150, 150)
        Range("C2:C22").Interior.Color = RGB(200, 200, 200)
    End If
    If Target.Address = "C2:C22" Then
        Range("D2:D22").Interior.Color = RGB(200, 160, 35)
        Range("D2:D22").Value = "No"
        Range("B2:B22").Font.Color = RGB(150, 150, 150)
        Range("B2:B22").Interior.Color = RGB(200, 200, 200)
     End If
End Sub

Ver 2

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Address = "B2:B22" Then
        Range("B2:B22").Interior.Color = RGB(200, 160, 35)
        Range("D2:D22").Value = "Yes"
        Range("C2:C22").Font.Color = RGB(150, 150, 150)
        Range("C2:C22").Interior.Color = RGB(200, 200, 200)
    End If
    If Target.Address = "C2:C22" Then
        Range("D2:D22").Interior.Color = RGB(200, 160, 35)
        Range("D2:D22").Value = "No"
        Range("B2:B22").Font.Color = RGB(150, 150, 150)
        Range("B2:B22").Interior.Color = RGB(200, 200, 200)
     End If
End Sub

Ok, the fundamental question I was asking I fixed and I am an idiot. I was in design mode. Sorry.


But I'm still trying to make this work as we speak, so if I've done something wrong on my code, please let me know.

Originally Posted by Blokeman

Is this what you're after? This is only for 1 row.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("D2:D22")) Is Nothing Then Exit Sub

If Range("D2").Value = "" Then
    Range("B2").Interior.ColorIndex = xlNone
    Range("B2").Font.ColorIndex = xlNone
    Range("B2").Font.Color = RGB(0, 0, 0)
    Range("C2").Interior.ColorIndex = xlNone
    Range("C2").Font.ColorIndex = xlNone
    Range("C2").Font.Color = RGB(0, 0, 0)
    Else
        If Range("D2").Value = "Yes" Then
        Range("B2").Interior.Color = RGB(200, 160, 35)
        Range("B2").Font.Color = RGB(0, 0, 0)
        Range("C2").Interior.Color = RGB(200, 200, 200)
        Range("C2").Font.Color = RGB(150, 150, 150)
        Else
        If Range("D2").Value = "No" Then
        Range("C2").Font.Color = RGB(0, 0, 0)
        Range("C2").Interior.Color = RGB(200, 160, 35)
        Range("B2").Interior.Color = RGB(200, 200, 200)
        Range("B2").Font.Color = RGB(150, 150, 150)
     End If
End If
End If

End Sub

Thanks for the response Blokeman. It took some doing, but I actually did get the whole thing figured out yesterday, for all the cells. My code is below. I focused on actions off of the active cell, the one the user has clicked, and used Offsets to change things nearby.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("$B$2:$B$22")) Is Nothing Then
With ActiveCell
        .Interior.Color = RGB(72, 135, 129)
        .Font.Color = RGB(0, 0, 0)
        .Offset(0, 2).Value = "Yes"
        .Offset(0, 1).Font.Color = RGB(150, 150, 150)
        .Offset(0, 1).Interior.Color = RGB(200, 200, 200)
End With
End If
If Not Intersect(Target, Range("$C$2:$C$22")) Is Nothing Then
With ActiveCell
        .Interior.Color = RGB(231, 176, 134)
        .Font.Color = RGB(0, 0, 0)
        .Offset(0, 1).Value = "No"
        .Offset(0, -1).Font.Color = RGB(150, 150, 150)
        .Offset(0, -1).Interior.Color = RGB(200, 200, 200)
End With
End If
End Sub