See attached file. Be careful not to click the edges of the cells else excel will move the active cell without performing the double click event.
This is the macro I added:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim sh1 As Worksheet, sh2 As Worksheet
Dim maxCol As Integer
Dim memoRange As String, memoDirection As String
Dim myRange As String, myDirection As String
Dim destCol As Integer, destRow As Long
'---- parameters ----
memoRange = "z1"
memoDirection = "z2"
maxCol = 8
'--------------------
Set sh1 = ThisWorkbook.Sheets(1)
Set sh2 = ThisWorkbook.Sheets(2)
myRange = sh2.Range(memoRange)
myDirection = sh2.Range(memoDirection)
If myRange = "" Then
myRange = "a1"
myDirection = 1
End If
Cancel = True
Target.Copy sh1.Range(myRange)
Target.Interior.ColorIndex = 3
Target.Font.Strikethrough = True
'calculating next destination cell
destCol = Range(myRange).Column
destRow = Range(myRange).Row
destCol = destCol + myDirection
If destCol = 0 Then
myDirection = 1
destCol = 1
destRow = destRow + 1
ElseIf destCol > maxCol Then
myDirection = -1
destCol = maxCol
destRow = destRow + 1
End If
'store next destination range and direction
sh2.Range(memoRange) = Cells(destRow, destCol).Address(0, 0)
sh2.Range(memoDirection) = myDirection
End Sub
Regards,
Antonio
Bookmarks