Creating Border on Offset Range (Static)

[stoutbn]

Hi, I've been searching all day for answers to how I can pull this off. I have two tables of data where records match up when the tables are placed side by side. I have a code that currently highlights the entire row of the sheet for which cell I have active. Works great. But I also want to have the cell that is offset 13 cells to the right of my active cell to have a border around it. I believe the code needs to be declared as static because when I move to a new cell I want the border to disappear and reappear in the new active cell's relative 13 cells to the right. Here is my code below. I get an error saying "object required" when I try to run. PLEASE help. Thanks.



Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static rr
Static cc
Static qq


If cc <> "" Then
With Columns(cc).Interior
.ColorIndex = xlNone
End With
With Rows(rr).Interior
.ColorIndex = xlNone
End With
With qq.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone
End With

End If

r = Selection.Row
c = Selection.Column
rr = r
cc = c
q = ActiveCell.Offset(0, 13)
qq = q

With Rows(r).Interior
.ColorIndex = 35
.Pattern = xlAutomatic


With ActiveCell.Offset(0, 13).BorderAround(xlDouble)

End With
End With
End Sub

 

[Leith Ross]

Hello stoutbn,

This works...

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)


    Dim n As Integer
    
    Static LastCell As Range


        If Target.Cells.Count > 1 Then Exit Sub
        
        Application.EnableEvents = False
        
            If Not LastCell Is Nothing Then
                LastCell.EntireRow.Interior.ColorIndex = xlNone
                For n = 7 To 11
                    LastCell.Borders(n).LineStyle = xlLineStyleNone
                Next n
            End If
        
            With Target
                .EntireRow.Interior.ColorIndex = 35
                .Offset(0, 13).BorderAround LineStyle:=xlDouble
            End With
        
            Set LastCell = Target.Offset(0, 13)
        
        Application.EnableEvents = True


End Sub

 

[stoutbn]

Leith,

Thank you for the code! But I was unable to get this to work. I copy/pasted the code into VBA but nothing happened when I clicked from cell to cell.

 

[Rick Rothstein]

Leith,

Thank you for the code! But I was unable to get this to work. I copy/pasted the code into VBA but nothing happened when I clicked from cell to cell.

Leith posted event code (so it would work automatically when you double-click a cell), not a macro (which requires a human to run it). Here are instruction on how to implement it...

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.