Selected Answer
I modified the code in your 'Module1' as below.
Option Explicit
Sub WriteToBankRec()
' 13 Oct 2017
Dim ColSum As Double
Dim Ws As Worksheet
Dim Target As Range
Dim R As Long
If ActiveSheet Is Worksheets("GL") Then
ColSum = SumByColor("F")
If ColSum <> xlNone / 10000 + xlNone Then
Set Ws = Worksheets("Bank Rec")
With Ws
Set Target = .Cells(.Rows.Count, "B").End(xlUp)
R = Target.Row + 1
.Rows(R).Insert
.Cells(R, "B").Value = Target.Value + 1
' you can fill in all the cells here
.Cells(R, "G").Value = ColSum
End With
End If
Else
MsgBox "Please select a cell with coloured fill" & vbCr & _
"on the ""GL"" worksheet.", _
vbExclamation, "Procedural requirement"
End If
End Sub
Private Function SumByColor(ByVal Clm As String) As Double
' 13 Oct 2017
' return -4142.4142 if an uncoloured cell is selected
' the code first looks at the colorindex of the cell in
' column Clm in the row of the currently active cell.
' Then it totals all contiguous cells of same color in column Clm
Dim Ws As Worksheet
Dim SumRng As Range
Dim SumClm As Long
Dim ColIndex As Long ' ColorIndex is a value of Long data type
Dim Rstart As Long, Rend As Long
SumClm = Columns(Clm).Column
With ActiveCell
Set Ws = .Worksheet
Set SumRng = Ws.Cells(.Row, SumClm)
End With
With SumRng
ColIndex = .Interior.ColorIndex
If (ColIndex <> xlNone) Then
Rstart = .Row
If Rstart > 1 Then
Do While Ws.Cells(Rstart - 1, SumClm).Interior.ColorIndex = ColIndex
If Rstart = 1 Then Exit Do
Rstart = Rstart - 1
Loop
End If
Rend = .Row
Do While Ws.Cells(Rend + 1, SumClm).Interior.ColorIndex = ColIndex
Rend = Rend + 1
Loop
With Ws.Columns(SumClm)
Set SumRng = Range(.Cells(Rstart), .Cells(Rend))
End With
SumByColor = Application.Sum(SumRng)
Else
SumByColor = xlNone / 10000 + xlNone
End If
End With
End Function
Note that the function 'SumByColor' must be called with a string (for example "F") to indicate the column to be summed. The function will return -4142.4142 if an uncoloured cell was selected which will prevent the result from being processed.
Observe that the function 'SumByColor' is private in scope, the sub 'WriteToBankRec' is public. You can call the public sub from the worksheet but the private function only from another procedure in Module1.