I think this should work, I've tried to keep it simple with comments showing what you might need to edit.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Dim rng As Range
Set rng = Range("G24:G25") ' destination range
' note that if you use 2 separate cells in the range, i.e. Range("G24,J24") then you will need to change
' rng(1) to rng.Areas(1) and rng(2) to rng.Areas(2)
If Target.Column = 1 Then rng.ClearContents: Exit Sub
Select Case Target.Column Mod 5
Case Is = 1
' the offsets in this section are relative to double clicking in columns F, K, P, etc
' rng(1) refers to the first cell in the destination range, this is where the list of the cell values is shown
' to show the results in the opposite cells, change rng(1) to rng(2)
' Target.Offset(6, -4) sets the position of the paired cell, 6 rows below the target, 4 columns left
' positive numbers offset down or right, negatives offset up or left. rows before the comma, columns after.
rng(1) = IIf(rng(1) = "", "", rng(1) & ", ") & Target & ", " & Target.Offset(6, -4)
' rng(2) to refers to the second cell in the range, this is where the sum will be shown
' to show the results in the opposite cells, change rng(2) to rng(1)
rng(2) = rng(2) + (Target - Target.Offset(6, -4))
Case Is = 2
' the offsets in this section are relative to double clicking in columns B, G, L, etc
rng(1) = IIf(rng(1) = "", "", rng(1) & ", ") & Target & ", " & Target.Offset(6, 4)
rng(2) = rng(2) + Target.Value + Target.Offset(6, 4)
Case Else
rng.ClearContents
End Select
End Sub
Note that I haven't included any error handling in the code. If the double clicked cell, or the related offset cell conatins text then the code will fail. The same would happen if the offset related to the clicked cell refers to an invalid range, i.e. if your row offset was -4 and you double clicked in row 3.
Hope all that makes sense.
Bookmarks