CODEWITHSUNDEEP
vbaexcel
dqcarlos
dqcarlos

Reputation: 11

VBA (Excel): Jump to (or remain) cell in column

I would like to start off with stating that I have virtually no coding experience. I found a VBA snippet online for highlighting an entire selected range (just to as a visual guide):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    Application.ScreenUpdating = False
    ' Clear the color of all the cells
    Cells.Interior.ColorIndex = 0
    With Target
        ' Highlight the entire column that contain the active cell
        .EntireRow.Interior.ColorIndex = 8

    End With
    Application.ScreenUpdating = True
End Sub

I would like to also have the cursor jump-to column "J". For instance, after performing a search for cells containing the words "strawberry topping" after pressing 'OK' the cell containing that text becomes active and, due to the VBA code, the entire row is highlighted.

The first cell I need to work on is in column "J". Can I also have column J selected along with the row being highlighted?

Thank you so much for your time and would appreciate any help you may have to offer.

Upvotes: 1

Views: 1551

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149297

My Three cents

  1. If you are using xl2007+ then do not use Target.Cells.Count. Use Target.Cells.CountLarge else you will get an Overflow error if a user tries to select all cells by pressing CTRL + A as Target.Cells.Count can't hold a Long value.
  2. If you want to select the row and the column, you might want to switch off events else you might end up in endless loop.
  3. Since you are working with events, use error handling.

Is this what you are trying?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Rw As Long, Col As Long
    Dim ColName As String
    On Error GoTo Whoa

    If Target.Cells.CountLarge > 1 Then Exit Sub

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    ' Clear the color of all the cells
    'Cells.Interior.ColorIndex = 0

    With Target
        Rw = .Row
        Col = .Column
        ColName = Split(Cells(, Col).Address, "$")(1)

        ' Highlight the entire column that contain the active cell
        '.EntireRow.Interior.ColorIndex = 8

        Range(ColName & ":" & ColName & "," & Rw & ":" & Rw).Select

    End With
LetsContinue:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub

Upvotes: 3

Related Questions