VBA Tweak

[dantheram]

Evening,

how do I stop the below code applying to the entire sheet, i wish for it to only take affect on a set range, in this instance g2:g35

  Private Sub Worksheet_SelectionChange(ByVal Target As Range)    'Task1: Show your active cell contents in cell E2
    actval = ActiveCell.Value
    actaddr = ActiveCell.Address
    Range("E2") = actval


    'Task2: Turn selected cell red


    'first set entire background white
    Range("g2:g35").Interior.Color = xlNone
    ActiveCell.Interior.ColorIndex = 3
   End Sub

 

[Scott Huish]

You should be referring to Target not Activecell, this is the range that is passed to the SelectionChange event. I also put in a check to see that only 1 cell was selected since you are writing the value to E2.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Range("G2:G35"), Target) Is Nothing Then
    Range("E2") = Target
    Range("G2:G35").Interior.Color = xlNone
    Target.Interior.Color = vbRed
End If
End Sub

 

[dantheram]

You should be referring to Target not Activecell, this is the range that is passed to the SelectionChange event. I also put in a check to see that only 1 cell was selected since you are writing the value to E2.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Range("G2:G35"), Target) Is Nothing Then
    Range("E2") = Target
    Range("G2:G35").Interior.Color = xlNone
    Target.Interior.Color = vbRed
End If
End Sub

Perfect!

thanks