Using Visual Basic to show active cell (column and row)

[thisisliam]

Hi all

I just implemented this method to show the active cell by highlighting the row and column.

Highlight active row/column in Excel without using VBA?

What I want to achieve is to highlight active row or column. I used VBA solutions but everytime Selection_change event is used I am loosing chance to undo any changes in my worksheet. Is there a ...

stackoverflow.com

While it works a treat, I don't like each cell in the the entire column and row being formatted. I'd like to just boarder the row and column. Using this method I'm only able to get it to boarder each and every cell within that row and column instead of just the column (left and right border) and row (top and bottom border). The selected (intersecting) cell by default is already outlined.

I would assume two conditional rules need to be defined, but I lack VBA knowledge and I'm not sure how I would modify it.

 

[Domenic]

Here are two different pieces of code. The first one should do as you've asked for. It outlines the selected row and column. Note, however, if your worksheet contains other borders, they will be removed. The second one highlights the row and column just like the one in your link. However, unlike the one in your link, it doesn't use conditional formatting and it doesn't require a re-calculation. Try both of them and you may find that you like the second one.

Here's the one with borders...

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Cells.Borders.LineStyle = xlNone
    With ActiveCell
        With .EntireRow
            With .Borders(xlEdgeTop)
                .Color = RGB(0, 176, 80) 'green (change as desired)
                .LineStyle = xlContinuous
                .Weight = xlMedium 'or xlThin, xlThick, and xlHairline (change as desired)
            End With
            With .Borders(xlEdgeBottom)
                .Color = RGB(0, 176, 80)
                .LineStyle = xlContinuous
                .Weight = xlMedium
            End With
        End With
        With .EntireColumn
            With .Borders(xlEdgeLeft)
                .Color = RGB(0, 176, 80)
                .LineStyle = xlContinuous
                .Weight = xlMedium
            End With
            With .Borders(xlEdgeRight)
                .Color = RGB(0, 176, 80)
                .LineStyle = xlContinuous
                .Weight = xlMedium
            End With
        End With
    End With
End Sub

And here's the one with highlighting...

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Cells.Interior.ColorIndex = xlNone
    With ActiveCell
        .EntireRow.Interior.Color = RGB(219, 229, 241)
        .EntireColumn.Interior.Color = RGB(219, 229, 241)
    End With
End Sub

Hope this helps!

 

[thisisliam]

@Domenic
The first one is perfect! Though you weren't wrong about it removing my other borders. Is this inevitable?

 

[Domenic]

If you already have existing borders around your data, then you'll need to amend the code so that after it clears all borders and before applying borders to the selected row and column, you re-draw the borders around your data.

However, I would suggest that you convert your data into a Table (Ribbon >> Insert tab >> Tables group >> Table). This way your table style will be maintained and there would be no need to re-draw your original borders every time.

 

[Michael M]

Why not just use

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Cells.Borders.LineStyle = xlNone
With ActiveCell.EntireRow.Borders
       .Color = RGB(0, 176, 80) 'green (change as desired)
       .LineStyle = xlContinuous
       .Weight = xlMedium 'or xlThin, xlThick, and xlHairline (change as desired)
End With
End Sub

 

[Domenic]

Hi Michael,

Just wanted to point out that the OP wanted borders around the column as well, and only an outline for both row and column, no inside borders...

I'd like to just boarder the row and column. Using this method I'm only able to get it to boarder each and every cell within that row and column instead of just the column (left and right border) and row (top and bottom border).

Cheers!

 

[Michael M]

@Domenic
Aha...thanks for pointing that out.
I was wondering why so much code for simply putting in borders.....?

 

[luisftv]

Domenic,​

Thanks for the code. Can you give us the code of how to do it if we need to be able to keep the existing borders? In some of our workbooks we cannot use AutoTables, we need to draw the borders manually. Therefore, we need this solution to be able not to remove the borders.

Thanks a million.

 

[Fluff]

Duplicate to: Border Around entire row of selected cell

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.