Home > Net >  Highlight surrounding cells of selected cell
Highlight surrounding cells of selected cell

Time:05-10

I am trying to exercise Levenshtein Distance in Excel. To fill the cells, we need to consider the minimum of three cells (left, up-left, and up). It is easy to find minimum of those three if they were highlighted.

I want to highlight those three cells whenever I put my cursor on any empty cell. Just like shown on image below. When I put my cursor on C3; B2, B3, and C2 should be higlighted.

I found a VBA script. But it higlightes the entire row and column of cursor cell. I am not familiar with VBA, therefore can't modify rows and columns to my way.

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Update 20200430
Static xRow
Static xColumn
If xColumn <> "" Then
    With Columns(xColumn).Interior
        .ColorIndex = xlNone
    End With
    With Rows(xRow).Interior
        .ColorIndex = xlNone
    End With
End If
pRow = Selection.Row
pColumn = Selection.Column
xRow = pRow
xColumn = pColumn
With Columns(pColumn).Interior
    .ColorIndex = 6
    .Pattern = xlSolid
End With
With Rows(pRow).Interior
    .ColorIndex = 6
    .Pattern = xlSolid
End With
End Sub

this is what it does

Output of code that Found

CodePudding user response:

A Worksheet SelectionChange: Highlight Cells

Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target.Cells(1)
        If .Row = 1 Then Exit Sub
        If .Column = 1 Then Exit Sub
        If IsEmpty(.Cells) Then
            .Worksheet.UsedRange.Interior.ColorIndex = xlNone
            Union(.Offset(-1, -1).Resize(2), .Offset(-1)) _
                .Interior.Color = vbYellow
        End If
    End With
End Sub
  • Related