Hi, I thought this was going to be simple …hoping for some help
I have an excel table (list-object) of customer addresses with columns like Customer, Address, City etc. Assume a cell (row) has already been selected in the "Address" column.
What I am after is a pair of macros that will increment/decrement the selected row up/down a row based on which macro is run. (will be assigned to menu buttons). The catch is that once the last row has been reached (if you're going down), it should select the top (first row) and then continue down again. Similarly, if you've been incrementing up, and reach the 1st row, then the last row should be selected and then it starts going up again.
These macros will work in conjunction with a Worksheet_SelectionChange event macro that currently formats the selected cell in the address column and also copies the value of it to another cell. Note that I added 'SelectedAddress' as a variable because I thought it might be useful for the Up/Down macros I'm trying to make. It's not actually used in the worksheet change event shown below.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim ws As Worksheet Dim SelectedAddress As String ' for potential use in the UP/DOWN macros If Not Intersect(Target, Range("tbl_Addresses[Address]")) Is Nothing Then ' if cell not blank/selected do this: Range("tbl_Addresses[Address]").Interior.ColorIndex = xlColorIndexNone ' set color to nothing With Target 'when cell in target range/column is selected do this: Range("F4").value = ActiveCell.value 'copy address to F4 With Selection.Interior 'change cell interior colour .Color = 6750207 '=light yellow End With SelectedAddress = Target.Address ' for potential use in UP/DOWN macros Call AddressSubmit 'processes address copied to F4 above End With End If End Sub