locked
Code available to all sheets in workbook RRS feed

  • Question

  • The following code highlights a row when a cell is selected.  I want this code to work for each sheet and newly created sheets in workbook.  How can I setup the code to do this? I thought it was a matter of moving the code from sheet1 to Workbook in <acronym title="vBulletin">VB</acronym>, but it doesn't work.
    Thanks in advance.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      Dim Rng As Range
      Set Rng = Range("A1:K1900")
      If Not Intersect(Target, Rng) Is Nothing Then
        Cells.Interior.ColorIndex = 0
        With Target
          Intersect(Target.EntireRow, Rng).Interior.ColorIndex = 6
          'Intersect(Target, Rng).EntireColumn.Interior.ColorIndex = 6
        End With
      End If
    End Sub

    Saturday, May 20, 2017 12:21 PM

Answers

  • Hi

    Place it in "ThisWorkbook"

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
      Dim Rng As Range
      Set Rng = Range("A1:K1900")
      If Not Intersect(Target, Rng) Is Nothing Then
        Cells.Interior.ColorIndex = 0
        With Target
          Intersect(Target.EntireRow, Rng).Interior.ColorIndex = 6
          'Intersect(Target, Rng).EntireColumn.Interior.ColorIndex = 6
        End With
      End If
    End Sub


    Cimjet

    • Marked as answer by falcios Saturday, May 20, 2017 1:52 PM
    Saturday, May 20, 2017 1:19 PM

All replies

  • Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    
        Dim Rng As Range
        
        Set Rng = Sh.Range("A1:K1900")
        
        If Not Intersect(Target, Rng) Is Nothing Then
            Sh.Cells.Interior.ColorIndex = 0
            With Target
                Intersect(Target.EntireRow, Rng).Interior.ColorIndex = 6
                'Intersect(Target, Rng).EntireColumn.Interior.ColorIndex = 6
            End With
        End If
    
    
    End Sub
    


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Saturday, May 20, 2017 1:18 PM
    Answerer
  • Hi

    Place it in "ThisWorkbook"

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
      Dim Rng As Range
      Set Rng = Range("A1:K1900")
      If Not Intersect(Target, Rng) Is Nothing Then
        Cells.Interior.ColorIndex = 0
        With Target
          Intersect(Target.EntireRow, Rng).Interior.ColorIndex = 6
          'Intersect(Target, Rng).EntireColumn.Interior.ColorIndex = 6
        End With
      End If
    End Sub


    Cimjet

    • Marked as answer by falcios Saturday, May 20, 2017 1:52 PM
    Saturday, May 20, 2017 1:19 PM
  • Thanks to you both. Worked great.
    Saturday, May 20, 2017 1:53 PM
  • You're welcome.

    Thanks for the feedback.


    Cimjet

    Saturday, May 20, 2017 1:57 PM