PLEASE HELP VBA - SO LONG NEEDS CONDENSING AND OPTIMIZATION

C

C Brook

New Member
Joined
Nov 22, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
THIS CODE WORKS GREAT BUT WHEN USING OTHER CELLS IT BUGS ALSO ITS RATHER LONG :( THANK YOU INADVANCE.

Private Sub Worksheet_Change(ByVal Target As Range)

' Check if the change occurred in cell B6 or B22 or B38 or B54

If Target.Address = "$B$6" Or Target.Address = "$B$22" Or Target.Address = "$B$38" Or _
Target.Address = "$B$54" Or Target.Address = "$B$70" Or Target.Address = "$B$86" Or _
Target.Address = "$B$102" Or Target.Address = "$B$118" Or Target.Address = "$B$134" Or _
Target.Address = "$B$150" Or Target.Address = "$B$166" Or Target.Address = "$B$182" Or _
Target.Address = "$B$198" Or Target.Address = "$B$214" Or Target.Address = "$B$230" Or _
Target.Address = "$B$246" Or Target.Address = "$B$262" Or Target.Address = "$B$278" Or _
Target.Address = "$B$294" Or Target.Address = "$B$310" Or Target.Address = "$B$326" Or _
Target.Address = "$B$342" Or Target.Address = "$B$358" Or Target.Address = "$B$374" Or _
Target.Address = "$B$390" Then
End If


' Clear existing comments in N17 and N6 or N33 and N22 or N49 or N38 and B54

If Target.Address = "$B$6" Then
Range("N17").ClearComments
Range("N6").ClearComments
ElseIf Target.Address = "$B$22" Then
Range("N33").ClearComments
Range("N22").ClearComments
ElseIf Target.Address = "$B$38" Then
Range("N49").ClearComments
Range("N38").ClearComments
ElseIf Target.Address = "$B$54" Then
Range("N65").ClearComments
Range("N54").ClearComments
ElseIf Target.Address = "$B$70" Then
Range("N81").ClearComments
Range("N70").ClearComments
ElseIf Target.Address = "$B$86" Then
Range("N97").ClearComments
Range("N86").ClearComments
ElseIf Target.Address = "$B$102" Then
Range("N113").ClearComments
Range("N102").ClearComments
ElseIf Target.Address = "$B$118" Then
Range("N129").ClearComments
Range("N118").ClearComments
ElseIf Target.Address = "$B$134" Then
Range("N145").ClearComments
Range("N134").ClearComments
ElseIf Target.Address = "$B$150" Then
Range("N161").ClearComments
Range("N150").ClearComments
ElseIf Target.Address = "$B$166" Then
Range("N177").ClearComments
Range("N166").ClearComments
ElseIf Target.Address = "$B$182" Then
Range("N193").ClearComments
Range("N182").ClearComments
ElseIf Target.Address = "$B$198" Then
Range("N209").ClearComments
Range("N198").ClearComments
ElseIf Target.Address = "$B$214" Then
Range("N225").ClearComments
Range("N214").ClearComments
ElseIf Target.Address = "$B$230" Then
Range("N241").ClearComments
Range("N230").ClearComments
ElseIf Target.Address = "$B$246" Then
Range("N257").ClearComments
Range("N246").ClearComments
ElseIf Target.Address = "$B$262" Then
Range("N273").ClearComments
Range("N262").ClearComments
ElseIf Target.Address = "$B$278" Then
Range("N289").ClearComments
Range("N278").ClearComments
ElseIf Target.Address = "$B$294" Then
Range("N305").ClearComments
Range("N294").ClearComments
ElseIf Target.Address = "$B$310" Then
Range("N321").ClearComments
Range("N310").ClearComments
ElseIf Target.Address = "$B$326" Then
Range("N337").ClearComments
Range("N326").ClearComments
ElseIf Target.Address = "$B$342" Then
Range("N353").ClearComments
Range("N342").ClearComments
ElseIf Target.Address = "$B$358" Then
Range("N369").ClearComments
Range("N358").ClearComments
ElseIf Target.Address = "$B$374" Then
Range("N385").ClearComments
Range("N374").ClearComments
ElseIf Target.Address = "$B$390" Then
Range("N401").ClearComments
Range("N390").ClearComments
End If
' Define ranges for suppliers (K10:K16), products (E10:E16), quantities (D10:D16), costs (M10:M16), and hyperlinks (L10:L16)
Dim supplierRange As Range
Dim productRange As Range
Dim quantityRange As Range
Dim costRange As Range
Dim hyperRange As Range
If Target.Address = "$B$6" Then
Set supplierRange = Range("K10:K16")
Set productRange = Range("E10:E16")
Set quantityRange = Range("D10:D16")
Set costRange = Range("M10:M16")
Set hyperRange = Range("L10:L16")

ElseIf Target.Address = "$B$22" Then

Set supplierRange = Range("K26:K32")
Set productRange = Range("E26:E32")
Set quantityRange = Range("D26:D32")
Set costRange = Range("M26:M32")
Set hyperRange = Range("L26:L32")

ElseIf Target.Address = "$B$38" Then

Set supplierRange = Range("K42:K48")
Set productRange = Range("E42:E48")
Set quantityRange = Range("D42:D48")
Set costRange = Range("M42:M48")
Set hyperRange = Range("L42:L48")

ElseIf Target.Address = "$B$54" Then

Set supplierRange = Range("K58:K64")
Set productRange = Range("E58:E64")
Set quantityRange = Range("D58:D64")
Set costRange = Range("M58:M64")
Set hyperRange = Range("L58:L64")

ElseIf Target.Address = "$B$70" Then

Set supplierRange = Range("K74:K80")
Set productRange = Range("E74:E80")
Set quantityRange = Range("D74:D80")
Set costRange = Range("M74:M80")
Set hyperRange = Range("L74:L80")

ElseIf Target.Address = "$B$86" Then

Set supplierRange = Range("K90:K96")
Set productRange = Range("E90:E96")
Set quantityRange = Range("D90:D96")
Set costRange = Range("M90:M96")
Set hyperRange = Range("L90:L96")

ElseIf Target.Address = "$B$102" Then

Set supplierRange = Range("K106:K112")
Set productRange = Range("E106:E112")
Set quantityRange = Range("D106:D112")
Set costRange = Range("M106:M112")
Set hyperRange = Range("L106:L112")

ElseIf Target.Address = "$B$118" Then

Set supplierRange = Range("K122:K128")
Set productRange = Range("E122:E128")
Set quantityRange = Range("D122:D128")
Set costRange = Range("M122:M128")
Set hyperRange = Range("L122:L128")

ElseIf Target.Address = "$B$134" Then

Set supplierRange = Range("K138:K144")
Set productRange = Range("E138:E144")
Set quantityRange = Range("D138:D144")
Set costRange = Range("M138:M144")
Set hyperRange = Range("L138:L144")

ElseIf Target.Address = "$B$150" Then

Set supplierRange = Range("K154:K160")
Set productRange = Range("E154:E160")
Set quantityRange = Range("D154:D160")
Set costRange = Range("M154:M160")
Set hyperRange = Range("L154:L160")

ElseIf Target.Address = "$B$166" Then

Set supplierRange = Range("K170:K176")
Set productRange = Range("E170:E176")
Set quantityRange = Range("D170:D176")
Set costRange = Range("M170:M176")
Set hyperRange = Range("L170:L176")

ElseIf Target.Address = "$B$182" Then

Set supplierRange = Range("K186:K192")
Set productRange = Range("E186:E192")
Set quantityRange = Range("D186:D192")
Set costRange = Range("M186:M192")
Set hyperRange = Range("L186:L192")

ElseIf Target.Address = "$B$198" Then

Set supplierRange = Range("K202:K208")
Set productRange = Range("E202:E208")
Set quantityRange = Range("D202:D208")
Set costRange = Range("M202:M208")
Set hyperRange = Range("L202:L208")

ElseIf Target.Address = "$B$214" Then

Set supplierRange = Range("K218:K224")
Set productRange = Range("E218:E224")
Set quantityRange = Range("D218:D224")
Set costRange = Range("M218:M224")
Set hyperRange = Range("L218:L224")

ElseIf Target.Address = "$B$230" Then

Set supplierRange = Range("K234:K240")
Set productRange = Range("E234:E240")
Set quantityRange = Range("D234:D240")
Set costRange = Range("M234:M240")
Set hyperRange = Range("L234:L240")

ElseIf Target.Address = "$B$246" Then

Set supplierRange = Range("K250:K256")
Set productRange = Range("E250:E256")
Set quantityRange = Range("D250:D256")
Set costRange = Range("M250:M256")
Set hyperRange = Range("L250:L256")

ElseIf Target.Address = "$B$262" Then

Set supplierRange = Range("K266:K272")
Set productRange = Range("E266:E272")
Set quantityRange = Range("D266:D272")
Set costRange = Range("M266:M272")
Set hyperRange = Range("L266:L272")

ElseIf Target.Address = "$B$278" Then

Set supplierRange = Range("K282:K288")
Set productRange = Range("E282:E288")
Set quantityRange = Range("D282:D288")
Set costRange = Range("M282:M288")
Set hyperRange = Range("L282:L288")

ElseIf Target.Address = "$B$294" Then

Set supplierRange = Range("K298:K304")
Set productRange = Range("E298:E304")
Set quantityRange = Range("D298:D304")
Set costRange = Range("M298:M304")
Set hyperRange = Range("L298:L304")

ElseIf Target.Address = "$B$310" Then

Set supplierRange = Range("K314:K320")
Set productRange = Range("E314:E320")
Set quantityRange = Range("D314:D320")
Set costRange = Range("M314:M320")
Set hyperRange = Range("L314:L320")

ElseIf Target.Address = "$B$326" Then

Set supplierRange = Range("K330:K336")
Set productRange = Range("E330:E336")
Set quantityRange = Range("D330:D336")
Set costRange = Range("M330:M336")
Set hyperRange = Range("L330:L336")

ElseIf Target.Address = "$B$342" Then

Set supplierRange = Range("K346:K352")
Set productRange = Range("E346:E352")
Set quantityRange = Range("D346:D352")
Set costRange = Range("M346:M352")
Set hyperRange = Range("L346:L352")

ElseIf Target.Address = "$B$358" Then

Set supplierRange = Range("K362:K368")
Set productRange = Range("E362:E368")
Set quantityRange = Range("D362:D368")
Set costRange = Range("M362:M368")
Set hyperRange = Range("L362:L368")

ElseIf Target.Address = "$B$374" Then

Set supplierRange = Range("K378:K384")
Set productRange = Range("E378:E384")
Set quantityRange = Range("D378:D384")
Set costRange = Range("M378:M384")
Set hyperRange = Range("L378:L384")

ElseIf Target.Address = "$B$390" Then

Set supplierRange = Range("K394:K400")
Set productRange = Range("E394:E400")
Set quantityRange = Range("D394:D400")
Set costRange = Range("M394:M400")
Set hyperRange = Range("L394:L400")

End If
' Create a dictionary to store unique products and corresponding quantities, costs, and hyperlinks for each supplier
Dim productsDict As Object
Set productsDict = CreateObject("Scripting.Dictionary")

' Loop through each cell in the supplier range
For Each cell In supplierRange
If Not cell Is Nothing Then ' Check if the cell exists
' Check if the supplier exists in the dictionary
If Not productsDict.Exists(cell.value) Then
' Check if the corresponding quantity, cost, and hyperlink cells have values
If quantityRange.Cells(cell.Row - quantityRange.Row + 1, 1).value <> "" And costRange.Cells(cell.Row - costRange.Row + 1, 1).value <> "" And hyperRange.Cells(cell.Row - hyperRange.Row + 1, 1).value <> "" Then
' If the quantity, cost, and hyperlink cells have values, add them to the dictionary with the corresponding product
productsDict.Add cell.value, quantityRange.Cells(cell.Row - quantityRange.Row + 1, 1).value & " - " & productRange.Cells(cell.Row - productRange.Row + 1, 1).value & " - £" & Format(costRange.Cells(cell.Row - costRange.Row + 1, 1).value, "0.00") & vbCrLf & hyperRange.Cells(cell.Row - hyperRange.Row + 1, 1).value
ElseIf quantityRange.Cells(cell.Row - quantityRange.Row + 1, 1).value <> "" And costRange.Cells(cell.Row - costRange.Row + 1, 1).value <> "" Then
' If only the quantity and cost cells have values, add them to the dictionary with the corresponding product
productsDict.Add cell.value, quantityRange.Cells(cell.Row - quantityRange.Row + 1, 1).value & " - " & productRange.Cells(cell.Row - productRange.Row + 1, 1).value & " - £" & Format(costRange.Cells(cell.Row - costRange.Row + 1, 1).value, "0.00")
ElseIf quantityRange.Cells(cell.Row - quantityRange.Row + 1, 1).value <> "" Then
' If only the quantity cell has a value, add it to the dictionary with the corresponding product
productsDict.Add cell.value, quantityRange.Cells(cell.Row - quantityRange.Row + 1, 1).value & " - " & productRange.Cells(cell.Row - productRange.Row + 1, 1).value
ElseIf costRange.Cells(cell.Row - costRange.Row + 1, 1).value <> "" Then
' If only the cost cell has a value, add it to the dictionary with the corresponding product
productsDict.Add cell.value, "£" & Format(costRange.Cells(cell.Row - costRange.Row + 1, 1).value, "0.00") & " - " & productRange.Cells(cell.Row - productRange.Row + 1, 1).value
ElseIf hyperRange.Cells(cell.Row - hyperRange.Row + 1, 1).value <> "" Then
' If only the hyperlink cell has a value, add it to the dictionary with the corresponding product
productsDict.Add cell.value, hyperRange.Cells(cell.Row - hyperRange.Row + 1, 1).value
End If
Else
' Check if the corresponding quantity, cost, and hyperlink cells have values
If quantityRange.Cells(cell.Row - quantityRange.Row + 1, 1).value <> "" And costRange.Cells(cell.Row - costRange.Row + 1, 1).value <> "" And hyperRange.Cells(cell.Row - hyperRange.Row + 1, 1).value <> "" Then
' If the quantity, cost, and hyperlink cells have values, append them to the existing value
productsDict(cell.value) = productsDict(cell.value) & vbCrLf & quantityRange.Cells(cell.Row - quantityRange.Row + 1, 1).value & " - " & productRange.Cells(cell.Row - productRange.Row + 1, 1).value & " - £" & Format(costRange.Cells(cell.Row - costRange.Row + 1, 1).value, "0.00") & vbCrLf & hyperRange.Cells(cell.Row - hyperRange.Row + 1, 1).value
ElseIf quantityRange.Cells(cell.Row - quantityRange.Row + 1, 1).value <> "" And costRange.Cells(cell.Row - costRange.Row + 1, 1).value <> "" Then
' If only the quantity and cost cells have values, append them to the existing value
productsDict(cell.value) = productsDict(cell.value) & vbCrLf & quantityRange.Cells(cell.Row - quantityRange.Row + 1, 1).value & " - " & productRange.Cells(cell.Row - productRange.Row + 1, 1).value & " - £" & Format(costRange.Cells(cell.Row - costRange.Row + 1, 1).value, "0.00")
ElseIf quantityRange.Cells(cell.Row - quantityRange.Row + 1, 1).value <> "" Then
' If only the quantity cell has a value, append it to the existing value
productsDict(cell.value) = productsDict(cell.value) & vbCrLf & quantityRange.Cells(cell.Row - quantityRange.Row + 1, 1).value & " - " & productRange.Cells(cell.Row - productRange.Row + 1, 1).value
ElseIf costRange.Cells(cell.Row - costRange.Row + 1, 1).value <> "" Then
' If only the cost cell has a value, append it to the existing value
productsDict(cell.value) = productsDict(cell.value) & vbCrLf & "£" & Format(costRange.Cells(cell.Row - costRange.Row + 1, 1).value, "0.00") & " - " & productRange.Cells(cell.Row - productRange.Row + 1, 1).value
ElseIf hyperRange.Cells(cell.Row - hyperRange.Row + 1, 1).value <> "" Then
' If only the hyperlink cell has a value, append it to the existing value
productsDict(cell.value) = productsDict(cell.value) & vbCrLf & hyperRange.Cells(cell.Row - hyperRange.Row + 1, 1).value
End If
End If
End If
Next cell


' Create the comment text for cell N17 or N33 or N49 or N54
Dim commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 As String
For Each key In productsDict.Keys
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & key & ":" & vbCrLf & productsDict(key) & vbCrLf & vbCrLf
Next key
' Include the value of cell N17 or N33 or N49 at the end of the comment

If Target.Address = "$B$6" Then
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N17").value, "0.00")
Dim commentShapeN17 As Shape
Set commentShapeN17 = Range("N17").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN17.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN17.Height = 300 ' Set the desired height in points
commentShapeN17.Width = 300 ' Set the desired width in points

ElseIf Target.Address = "$B$22" Then
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N33").value, "0.00")
Dim commentShapeN33 As Shape
Set commentShapeN33 = Range("N33").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN33.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN33.Height = 300 ' Set the desired height in points
commentShapeN33.Width = 300 ' Set the desired width in points

ElseIf Target.Address = "$B$38" Then
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N49").value, "0.00")
Dim commentShapeN49 As Shape
Set commentShapeN49 = Range("N49").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN49.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN49.Height = 300 ' Set the desired height in points
commentShapeN49.Width = 300 ' Set the desired width in points

ElseIf Target.Address = "$B$54" Then
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N65").value, "0.00")
Dim commentShapeN65 As Shape
Set commentShapeN65 = Range("N65").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN65.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN65.Height = 300 ' Set the desired height in points
commentShapeN65.Width = 300 ' Set the desired width in points


ElseIf Target.Address = "$B$70" Then
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N81").value, "0.00")
Dim commentShapeN81 As Shape
Set commentShapeN81 = Range("N81").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN81.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN81.Height = 300 ' Set the desired height in points
commentShapeN81.Width = 300 ' Set the desired width in points

ElseIf Target.Address = "$B$86" Then

commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N97").value, "0.00")
Dim commentShapeN97 As Shape
Set commentShapeN97 = Range("N97").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN97.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN97.Height = 300 ' Set the desired height in points
commentShapeN97.Width = 300 ' Set the desired width in points

ElseIf Target.Address = "$B$102" Then
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N113").value, "0.00")
Dim commentShapeN113 As Shape
Set commentShapeN113 = Range("N113").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN113.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN113.Height = 300 ' Set the desired height in points
commentShapeN113.Width = 300 ' Set the desired width in points

ElseIf Target.Address = "$B$118" Then
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N129").value, "0.00")
Dim commentShapeN129 As Shape
Set commentShapeN129 = Range("N129").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN129.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN129.Height = 300 ' Set the desired height in points
commentShapeN129.Width = 300 ' Set the desired width in points

ElseIf Target.Address = "$B$134" Then
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N145").value, "0.00")
Dim commentShapeN145 As Shape
Set commentShapeN145 = Range("N145").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN145.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN145.Height = 300 ' Set the desired height in points
commentShapeN145.Width = 300 ' Set the desired width in points

ElseIf Target.Address = "$B$150" Then
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N161").value, "0.00")
Dim commentShapeN161 As Shape
Set commentShapeN161 = Range("N161").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN161.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN161.Height = 300 ' Set the desired height in points
commentShapeN161.Width = 300 ' Set the desired width in point

ElseIf Target.Address = "$B$166" Then
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N177").value, "0.00")
Dim commentShapeN177 As Shape
Set commentShapeN177 = Range("N177").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN177.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN177.Height = 300 ' Set the desired height in points
commentShapeN177.Width = 300 ' Set the desired width in point

ElseIf Target.Address = "$B$182" Then
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N193").value, "0.00")
Dim commentShapeN193 As Shape
Set commentShapeN193 = Range("N193").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN193.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN193.Height = 300 ' Set the desired height in points
commentShapeN193.Width = 300 ' Set the desired width in point

ElseIf Target.Address = "$B$198" Then
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N209").value, "0.00")
Dim commentShapeN209 As Shape
Set commentShapeN209 = Range("N209").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN209.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN209.Height = 300 ' Set the desired height in points
commentShapeN209.Width = 300 ' Set the desired width in point

ElseIf Target.Address = "$B$214" Then
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N225").value, "0.00")
Dim commentShapeN225 As Shape
Set commentShapeN225 = Range("N225").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN225.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN225.Height = 300 ' Set the desired height in points
commentShapeN225.Width = 300 ' Set the desired width in point

ElseIf Target.Address = "$B$230" Then
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N241").value, "0.00")
Dim commentShapeN241 As Shape
Set commentShapeN241 = Range("N241").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN241.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN241.Height = 300 ' Set the desired height in points
commentShapeN241.Width = 300 ' Set the desired width in point

ElseIf Target.Address = "$B$246" Then
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N257").value, "0.00")
Dim commentShapeN257 As Shape
Set commentShapeN257 = Range("N257").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN257.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN257.Height = 300 ' Set the desired height in points
commentShapeN257.Width = 300 ' Set the desired width in point

ElseIf Target.Address = "$B$262" Then
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N273").value, "0.00")
Dim commentShapeN273 As Shape
Set commentShapeN273 = Range("N273").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN273.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN273.Height = 300 ' Set the desired height in points
commentShapeN273.Width = 300 ' Set the desired width in point

ElseIf Target.Address = "$B$278" Then
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N289").value, "0.00")
Dim commentShapeN289 As Shape
Set commentShapeN289 = Range("N289").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN289.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN289.Height = 300 ' Set the desired height in points
commentShapeN289.Width = 300 ' Set the desired width in point

ElseIf Target.Address = "$B$294" Then
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N305").value, "0.00")
Dim commentShapeN305 As Shape
Set commentShapeN305 = Range("N305").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN305.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN305.Height = 300 ' Set the desired height in points
commentShapeN305.Width = 300 ' Set the desired width in point

ElseIf Target.Address = "$B$310" Then
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N321").value, "0.00")
Dim commentShapeN321 As Shape
Set commentShapeN321 = Range("N321").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN321.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN321.Height = 300 ' Set the desired height in points
commentShapeN321.Width = 300 ' Set the desired width in point

ElseIf Target.Address = "$B$326" Then
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N337").value, "0.00")
Dim commentShapeN337 As Shape
Set commentShapeN337 = Range("N337").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN337.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN337.Height = 300 ' Set the desired height in points
commentShapeN337.Width = 300 ' Set the desired width in point

ElseIf Target.Address = "$B$342" Then
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N353").value, "0.00")
Dim commentShapeN353 As Shape
Set commentShapeN353 = Range("N353").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN353.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN353.Height = 300 ' Set the desired height in points
commentShapeN353.Width = 300 ' Set the desired width in point

ElseIf Target.Address = "$B$358" Then
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N369").value, "0.00")
Dim commentShapeN369 As Shape
Set commentShapeN369 = Range("N369").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN369.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN369.Height = 300 ' Set the desired height in points
commentShapeN369.Width = 300 ' Set the desired width in point

ElseIf Target.Address = "$B$374" Then
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N385").value, "0.00")
Dim commentShapeN385 As Shape
Set commentShapeN385 = Range("N385").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN385.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN385.Height = 300 ' Set the desired height in points
commentShapeN385.Width = 300 ' Set the desired width in point

ElseIf Target.Address = "$B$390" Then
commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 = commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401 & "Each: £" & Format(Range("N401").value, "0.00")
Dim commentShapeN401 As Shape
Set commentShapeN401 = Range("N401").AddComment(commentTextN17OrN33OrN49OrN65OrN81OrN97OrN113OrN129OrN145OrN161OrN177OrN193OrN209OrN225OrN241OrN257OrN273OrN289OrN321OrN305OrN337OrN353OrN369OrN385OrN401).Shape
commentShapeN401.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN401.Height = 300 ' Set the desired height in points
commentShapeN401.Width = 300 ' Set the desired width in point

End If
' Create the comment text for cell N6 or N22 or N38 or N54
Dim commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 As String

If Target.Address = "$B$6" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B8").value & " a day. "
Dim commentShapeN6 As Shape
On Error Resume Next ' In case N6 doesn't have an existing comment
Set commentShapeN6 = Range("N6").Comment.Shape
On Error GoTo 0

If commentShapeN6 Is Nothing Then
Set commentShapeN6 = Range("N6").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN6.TextFrame.AutoSize = True
commentShapeN6.Height = 25 ' Set the desired height in points
commentShapeN6.Width = 100 ' Set the desired width in points
Else
commentShapeN6.TextFrame.Characters.Text = commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390
End If
ElseIf Target.Address = "$B$22" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B24").value & " a day. "
Dim commentShapeN22 As Shape
On Error Resume Next ' In case N22 doesn't have an existing comment
Set commentShapeN22 = Range("N22").Comment.Shape
On Error GoTo 0
If commentShapeN22 Is Nothing Then
Set commentShapeN22 = Range("N22").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN22.TextFrame.AutoSize = True
commentShapeN22.Height = 25 ' Set the desired height in points
commentShapeN22.Width = 100 ' Set the desired width in points
Else
commentShapeN22.TextFrame.Characters.Text = commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390
End If
ElseIf Target.Address = "$B$38" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B40").value & " a day. "
Dim commentShapeN38 As Shape
On Error Resume Next ' In case N38 doesn't have an existing comment
Set commentShapeN38 = Range("N38").Comment.Shape
On Error GoTo 0
If commentShapeN38 Is Nothing Then
Set commentShapeN38 = Range("N38").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN38.TextFrame.AutoSize = True
commentShapeN38.Height = 25 ' Set the desired height in points
commentShapeN38.Width = 100 ' Set the desired width in points
End If
ElseIf Target.Address = "$B$54" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B56").value & " a day. "
Dim commentShapeN54 As Shape
On Error Resume Next ' In case N54 doesn't have an existing comment
Set commentShapeN54 = Range("N54").Comment.Shape
On Error GoTo 0
If commentShapeN54 Is Nothing Then
Set commentShapeN54 = Range("N54").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN54.TextFrame.AutoSize = True
commentShapeN54.Height = 25 ' Set the desired height in points
commentShapeN54.Width = 100 ' Set the desired width in points
Else
commentShapeN54.TextFrame.Characters.Text = commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390
End If
ElseIf Target.Address = "$B$70" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B72").value & " a day. "
Dim commentShapeN70 As Shape
On Error Resume Next ' In case N70 doesn't have an existing comment
Set commentShapeN70 = Range("N70").Comment.Shape
On Error GoTo 0

If commentShapeN70 Is Nothing Then
Set commentShapeN70 = Range("N70").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN70.TextFrame.AutoSize = True
commentShapeN70.Height = 25 ' Set the desired height in points
commentShapeN70.Width = 100 ' Set the desired width in points
Else
commentShapeN70.TextFrame.Characters.Text = commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390
End If

ElseIf Target.Address = "$B$86" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B88").value & " a day. "
Dim commentShapeN86 As Shape
On Error Resume Next ' In case N86 doesn't have an existing comment
Set commentShapeN86 = Range("N86").Comment.Shape
On Error GoTo 0

If commentShapeN86 Is Nothing Then
Set commentShapeN86 = Range("N86").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN86.TextFrame.AutoSize = True
commentShapeN86.Height = 25 ' Set the desired height in points
commentShapeN86.Width = 100 ' Set the desired width in points
Else
commentShapeN86.TextFrame.Characters.Text = commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390
End If

ElseIf Target.Address = "$B$102" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B104").value & " a day. "
Dim commentShapeN102 As Shape
On Error Resume Next ' In case N102 doesn't have an existing comment
Set commentShapeN102 = Range("N102").Comment.Shape
On Error GoTo 0

If commentShapeN102 Is Nothing Then
Set commentShapeN102 = Range("N102").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN102.TextFrame.AutoSize = True
commentShapeN102.Height = 25 ' Set the desired height in points
commentShapeN102.Width = 100 ' Set the desired width in points
Else
commentShapeN102.TextFrame.Characters.Text = commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390
End If

ElseIf Target.Address = "$B$118" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B120").value & " a day. "
Dim commentShapeN118 As Shape
On Error Resume Next ' In case N118 doesn't have an existing comment
Set commentShapeN118 = Range("N118").Comment.Shape
On Error GoTo 0

If commentShapeN118 Is Nothing Then
Set commentShapeN118 = Range("N118").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN118.TextFrame.AutoSize = True
commentShapeN118.Height = 25 ' Set the desired height in points
commentShapeN118.Width = 100 ' Set the desired width in points
Else
commentShapeN118.TextFrame.Characters.Text = commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390
End If

ElseIf Target.Address = "$B$134" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B136").value & " a day. "
Dim commentShapeN134 As Shape
On Error Resume Next ' In case N134 doesn't have an existing comment
Set commentShapeN134 = Range("N134").Comment.Shape
On Error GoTo 0

If commentShapeN134 Is Nothing Then
Set commentShapeN134 = Range("N134").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN134.TextFrame.AutoSize = True
commentShapeN134.Height = 25 ' Set the desired height in points
commentShapeN134.Width = 100 ' Set the desired width in points
Else
commentShapeN134.TextFrame.Characters.Text = commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390
End If

ElseIf Target.Address = "$B$150" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B152").value & " a day. "
Dim commentShapeN150 As Shape
On Error Resume Next ' In case N150 doesn't have an existing comment
Set commentShapeN150 = Range("N150").Comment.Shape
On Error GoTo 0

If commentShapeN150 Is Nothing Then
Set commentShapeN150 = Range("N150").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN150.TextFrame.AutoSize = True
commentShapeN150.Height = 25 ' Set the desired height in points
commentShapeN150.Width = 100 ' Set the desired width in points
Else
commentShapeN150.TextFrame.Characters.Text = commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390
End If

ElseIf Target.Address = "$B$166" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B168").value & " a day. "
Dim commentShapeN166 As Shape
On Error Resume Next ' In case N166 doesn't have an existing comment
Set commentShapeN166 = Range("N166").Comment.Shape
On Error GoTo 0

If commentShapeN166 Is Nothing Then
Set commentShapeN166 = Range("N166").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN166.TextFrame.AutoSize = True
commentShapeN166.Height = 25 ' Set the desired height in points
commentShapeN166.Width = 100 ' Set the desired width in points
Else
commentShapeN166.TextFrame.Characters.Text = commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390
End If

ElseIf Target.Address = "$B$182" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B184").value & " a day. "
Dim commentShapeN182 As Shape
On Error Resume Next ' In case N182 doesn't have an existing comment
Set commentShapeN182 = Range("N182").Comment.Shape
On Error GoTo 0

If commentShapeN182 Is Nothing Then
Set commentShapeN182 = Range("N182").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN182.TextFrame.AutoSize = True
commentShapeN182.Height = 25 ' Set the desired height in points
commentShapeN182.Width = 100 ' Set the desired width in points
Else
commentShapeN182.TextFrame.Characters.Text = commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390
End If

ElseIf Target.Address = "$B$198" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B200").value & " a day. "
Dim commentShapeN198 As Shape
On Error Resume Next ' In case N198 doesn't have an existing comment
Set commentShapeN198 = Range("N198").Comment.Shape
On Error GoTo 0

If commentShapeN198 Is Nothing Then
Set commentShapeN198 = Range("N198").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN198.TextFrame.AutoSize = True
commentShapeN198.Height = 25 ' Set the desired height in points
commentShapeN198.Width = 100 ' Set the desired width in points
Else
commentShapeN198.TextFrame.Characters.Text = commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390
End If

ElseIf Target.Address = "$B$214" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B216").value & " a day. "
Dim commentShapeN214 As Shape
On Error Resume Next ' In case N214 doesn't have an existing comment
Set commentShapeN214 = Range("N214").Comment.Shape
On Error GoTo 0

If commentShapeN214 Is Nothing Then
Set commentShapeN214 = Range("N214").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN214.TextFrame.AutoSize = True
commentShapeN214.Height = 25 ' Set the desired height in points
commentShapeN214.Width = 100 ' Set the desired width in points
Else
commentShapeN214.TextFrame.Characters.Text = commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390
End If

ElseIf Target.Address = "$B$230" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B232").value & " a day. "
Dim commentShapeN230 As Shape
On Error Resume Next ' In case N230 doesn't have an existing comment
Set commentShapeN230 = Range("N230").Comment.Shape
On Error GoTo 0

If commentShapeN230 Is Nothing Then
Set commentShapeN230 = Range("N230").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN230.TextFrame.AutoSize = True
commentShapeN230.Height = 25 ' Set the desired height in points
commentShapeN230.Width = 100 ' Set the desired width in points
Else
commentShapeN214.TextFrame.Characters.Text = commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390
End If

ElseIf Target.Address = "$B$246" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B248").value & " a day. "
Dim commentShapeN246 As Shape
On Error Resume Next ' In case N246 doesn't have an existing comment
Set commentShapeN246 = Range("N246").Comment.Shape
On Error GoTo 0

If commentShapeN246 Is Nothing Then
Set commentShapeN246 = Range("N246").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN246.TextFrame.AutoSize = True
commentShapeN246.Height = 25 ' Set the desired height in points
commentShapeN246.Width = 100 ' Set the desired width in points
Else
commentShapeN246.TextFrame.Characters.Text = commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390
End If

ElseIf Target.Address = "$B$262" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B264").value & " a day. "
Dim commentShapeN262 As Shape
On Error Resume Next ' In case N262 doesn't have an existing comment
Set commentShapeN262 = Range("N262").Comment.Shape
On Error GoTo 0

If commentShapeN262 Is Nothing Then
Set commentShapeN262 = Range("N262").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN262.TextFrame.AutoSize = True
commentShapeN262.Height = 25 ' Set the desired height in points
commentShapeN262.Width = 100 ' Set the desired width in points
Else
commentShapeN262.TextFrame.Characters.Text = commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390
End If

ElseIf Target.Address = "$B$278" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B280").value & " a day. "
Dim commentShapeN278 As Shape
On Error Resume Next ' In case N278 doesn't have an existing comment
Set commentShapeN278 = Range("N278").Comment.Shape
On Error GoTo 0

If commentShapeN278 Is Nothing Then
Set commentShapeN278 = Range("N278").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN278.TextFrame.AutoSize = True
commentShapeN278.Height = 25 ' Set the desired height in points
commentShapeN278.Width = 100 ' Set the desired width in points
Else
commentShapeN278.TextFrame.Characters.Text = commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390
End If

ElseIf Target.Address = "$B$294" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B296").value & " a day. "
Dim commentShapeN294 As Shape
On Error Resume Next ' In case N294 doesn't have an existing comment
Set commentShapeN294 = Range("N294").Comment.Shape
On Error GoTo 0

If commentShapeN294 Is Nothing Then
Set commentShapeN294 = Range("N294").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN294.TextFrame.AutoSize = True
commentShapeN294.Height = 25 ' Set the desired height in points
commentShapeN294.Width = 100 ' Set the desired width in points
Else
commentShapeN294.TextFrame.Characters.Text = commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390
End If

ElseIf Target.Address = "$B$310" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B312").value & " a day. "
Dim commentShapeN310 As Shape
On Error Resume Next ' In case N310 doesn't have an existing comment
Set commentShapeN310 = Range("N310").Comment.Shape
On Error GoTo 0

If commentShapeN310 Is Nothing Then
Set commentShapeN310 = Range("N310").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN310.TextFrame.AutoSize = True
commentShapeN310.Height = 25 ' Set the desired height in points
commentShapeN310.Width = 100 ' Set the desired width in points
Else
commentShapeN310.TextFrame.Characters.Text = commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390
End If


ElseIf Target.Address = "$B$326" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B328").value & " a day. "
Dim commentShapeN326 As Shape
On Error Resume Next ' In case N326 doesn't have an existing comment
Set commentShapeN326 = Range("N326").Comment.Shape
On Error GoTo 0

If commentShapeN326 Is Nothing Then
Set commentShapeN326 = Range("N326").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN326.TextFrame.AutoSize = True
commentShapeN326.Height = 25 ' Set the desired height in points
commentShapeN326.Width = 100 ' Set the desired width in points
Else
commentShapeN326.TextFrame.Characters.Text = commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390
End If


ElseIf Target.Address = "$B$342" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B344").value & " a day. "
Dim commentShapeN342 As Shape
On Error Resume Next ' In case N342 doesn't have an existing comment
Set commentShapeN342 = Range("N342").Comment.Shape
On Error GoTo 0

If commentShapeN342 Is Nothing Then
Set commentShapeN342 = Range("N342").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN342.TextFrame.AutoSize = True
commentShapeN342.Height = 25 ' Set the desired height in points
commentShapeN342.Width = 100 ' Set the desired width in points
Else
commentShapeN342.TextFrame.Characters.Text = commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390
End If

ElseIf Target.Address = "$B$358" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B360").value & " a day. "
Dim commentShapeN358 As Shape
On Error Resume Next ' In case N358 doesn't have an existing comment
Set commentShapeN358 = Range("N358").Comment.Shape
On Error GoTo 0

If commentShapeN358 Is Nothing Then
Set commentShapeN358 = Range("N358").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN358.TextFrame.AutoSize = True
commentShapeN358.Height = 25 ' Set the desired height in points
commentShapeN358.Width = 100 ' Set the desired width in points
Else
commentShapeN358.TextFrame.Characters.Text = commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390
End If

ElseIf Target.Address = "$B$374" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B376").value & " a day. "
Dim commentShapeN374 As Shape
On Error Resume Next ' In case N374 doesn't have an existing comment
Set commentShapeN374 = Range("N374").Comment.Shape
On Error GoTo 0

If commentShapeN374 Is Nothing Then
Set commentShapeN374 = Range("N374").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN374.TextFrame.AutoSize = True
commentShapeN374.Height = 25 ' Set the desired height in points
commentShapeN374.Width = 100 ' Set the desired width in points
Else
commentShapeN374.TextFrame.Characters.Text = commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390
End If
ElseIf Target.Address = "$B$390" Then
commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390 = "Based on " & Range("B392").value & " a day. "
Dim commentShapeN390 As Shape
On Error Resume Next ' In case N390 doesn't have an existing comment
Set commentShapeN390 = Range("N390").Comment.Shape
On Error GoTo 0

If commentShapeN390 Is Nothing Then
Set commentShapeN390 = Range("N390").AddComment(commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390).Shape
commentShapeN390.TextFrame.AutoSize = True
commentShapeN390.Height = 25 ' Set the desired height in points
commentShapeN390.Width = 100 ' Set the desired width in points
Else
commentShapeN390.TextFrame.Characters.Text = commentTextN6OrN22OrN38orN54OrN70OrN86OrN102OrN118OrN134OrN150OrN166OrN182OrN198OrN214OrN230OrN246OrN262OrN278OrN294OrN310OrN326OrN342OrN358OrN374Or390
End If
End If
End Sub
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Hey C Brook, Welcome to MrExcel.

This code is way too long and it would be hard for most of us to decipher what this code is trying to accomplish.

Would it be possible to explain what this code is doing and what you are trying to accomplish? If you can, provide an example with some sample data showing the expected results?
 
Upvote 0
Coyotex3 said:
Hey C Brook, Welcome to MrExcel.

This code is way too long and it would be hard for most of us to decipher what this code is trying to accomplish.

Would it be possible to explain what this code is doing and what you are trying to accomplish? If you can, provide an example with some sample data showing the expected results?
Click to expand...
Sorry this is without the ranges and only 2

Private Sub Worksheet_Change(ByVal Target As Range)
' Check if the change occurred in cell B6 or B22
If Target.Address = "$B$6" Or Target.Address = "$B$22" Then
' Clear existing comments in N17 and N6 or N33 and N22
If Target.Address = "$B$6" Then
Range("N17").ClearComments
Range("N6").ClearComments
ElseIf Target.Address = "$B$22" Then
Range("N33").ClearComments
Range("N22").ClearComments
End If

' Define ranges for suppliers (K10:K16), products (E10:E16), quantities (D10:D16), costs (M10:M16), and hyperlinks (L10:L16)
Dim supplierRange As Range
Dim productRange As Range
Dim quantityRange As Range
Dim costRange As Range
Dim hyperRange As Range
If Target.Address = "$B$6" Then
Set supplierRange = Range("K10:K16")
Set productRange = Range("E10:E16")
Set quantityRange = Range("D10:D16")
Set costRange = Range("M10:M16")
Set hyperRange = Range("L10:L16")
ElseIf Target.Address = "$B$22" Then
Set supplierRange = Range("K26:K32")
Set productRange = Range("E26:E32")
Set quantityRange = Range("D26:D32")
Set costRange = Range("M26:M32")
Set hyperRange = Range("L26:L32")
End If

' Create a dictionary to store unique products and corresponding quantities, costs, and hyperlinks for each supplier
Dim productsDict As Object
Set productsDict = CreateObject("Scripting.Dictionary")

' Loop through each cell in the supplier range
Dim cell As Range
For Each cell In supplierRange
' Check if the supplier exists in the dictionary
If Not productsDict.Exists(cell.value) Then
' Check if the corresponding quantity, cost, and hyperlink cells have values
If quantityRange.Cells(cell.Row - quantityRange.Row + 1, 1).value <> "" And costRange.Cells(cell.Row - costRange.Row + 1, 1).value <> "" And hyperRange.Cells(cell.Row - hyperRange.Row + 1, 1).value <> "" Then
' If the quantity, cost, and hyperlink cells have values, add them to the dictionary with the corresponding product
productsDict.Add cell.value, quantityRange.Cells(cell.Row - quantityRange.Row + 1, 1).value & " - " & productRange.Cells(cell.Row - productRange.Row + 1, 1).value & " - £" & Format(costRange.Cells(cell.Row - costRange.Row + 1, 1).value, "0.00") & vbCrLf & hyperRange.Cells(cell.Row - hyperRange.Row + 1, 1).value
ElseIf quantityRange.Cells(cell.Row - quantityRange.Row + 1, 1).value <> "" And costRange.Cells(cell.Row - costRange.Row + 1, 1).value <> "" Then
' If only the quantity and cost cells have values, add them to the dictionary with the corresponding product
productsDict.Add cell.value, quantityRange.Cells(cell.Row - quantityRange.Row + 1, 1).value & " - " & productRange.Cells(cell.Row - productRange.Row + 1, 1).value & " - £" & Format(costRange.Cells(cell.Row - costRange.Row + 1, 1).value, "0.00")
ElseIf quantityRange.Cells(cell.Row - quantityRange.Row + 1, 1).value <> "" Then
' If only the quantity cell has a value, add it to the dictionary with the corresponding product
productsDict.Add cell.value, quantityRange.Cells(cell.Row - quantityRange.Row + 1, 1).value & " - " & productRange.Cells(cell.Row - productRange.Row + 1, 1).value
ElseIf costRange.Cells(cell.Row - costRange.Row + 1, 1).value <> "" Then
' If only the cost cell has a value, add it to the dictionary with the corresponding product
productsDict.Add cell.value, "£" & Format(costRange.Cells(cell.Row - costRange.Row + 1, 1).value, "0.00") & " - " & productRange.Cells(cell.Row - productRange.Row + 1, 1).value
ElseIf hyperRange.Cells(cell.Row - hyperRange.Row + 1, 1).value <> "" Then
' If only the hyperlink cell has a value, add it to the dictionary with the corresponding product
productsDict.Add cell.value, hyperRange.Cells(cell.Row - hyperRange.Row + 1, 1).value
End If
Else
' Check if the corresponding quantity, cost, and hyperlink cells have values
If quantityRange.Cells(cell.Row - quantityRange.Row + 1, 1).value <> "" And costRange.Cells(cell.Row - costRange.Row + 1, 1).value <> "" And hyperRange.Cells(cell.Row - hyperRange.Row + 1, 1).value <> "" Then
' If the quantity, cost, and hyperlink cells have values, append them to the existing value
productsDict(cell.value) = productsDict(cell.value) & vbCrLf & quantityRange.Cells(cell.Row - quantityRange.Row + 1, 1).value & " - " & productRange.Cells(cell.Row - productRange.Row + 1, 1).value & " - £" & Format(costRange.Cells(cell.Row - costRange.Row + 1, 1).value, "0.00") & vbCrLf & hyperRange.Cells(cell.Row - hyperRange.Row + 1, 1).value
ElseIf quantityRange.Cells(cell.Row - quantityRange.Row + 1, 1).value <> "" And costRange.Cells(cell.Row - costRange.Row + 1, 1).value <> "" Then
' If only the quantity and cost cells have values, append them to the existing value
productsDict(cell.value) = productsDict(cell.value) & vbCrLf & quantityRange.Cells(cell.Row - quantityRange.Row + 1, 1).value & " - " & productRange.Cells(cell.Row - productRange.Row + 1, 1).value & " - £" & Format(costRange.Cells(cell.Row - costRange.Row + 1, 1).value, "0.00")
ElseIf quantityRange.Cells(cell.Row - quantityRange.Row + 1, 1).value <> "" Then
' If only the quantity cell has a value, append it to the existing value
productsDict(cell.value) = productsDict(cell.value) & vbCrLf & quantityRange.Cells(cell.Row - quantityRange.Row + 1, 1).value & " - " & productRange.Cells(cell.Row - productRange.Row + 1, 1).value
ElseIf costRange.Cells(cell.Row - costRange.Row + 1, 1).value <> "" Then
' If only the cost cell has a value, append it to the existing value
productsDict(cell.value) = productsDict(cell.value) & vbCrLf & "£" & Format(costRange.Cells(cell.Row - costRange.Row + 1, 1).value, "0.00") & " - " & productRange.Cells(cell.Row - productRange.Row + 1, 1).value
ElseIf hyperRange.Cells(cell.Row - hyperRange.Row + 1, 1).value <> "" Then
' If only the hyperlink cell has a value, append it to the existing value
productsDict(cell.value) = productsDict(cell.value) & vbCrLf & hyperRange.Cells(cell.Row - hyperRange.Row + 1, 1).value
End If
End If
Next cell

' Create the comment text for cell N17 or N33
Dim commentTextN17OrN33 As String
For Each key In productsDict.Keys
commentTextN17OrN33 = commentTextN17OrN33 & key & ":" & vbCrLf & productsDict(key) & vbCrLf & vbCrLf
Next key

' Include the value of cell N17 or N33 at the end of the comment
If Target.Address = "$B$6" Then
commentTextN17OrN33 = commentTextN17OrN33 & "Each: £" & Format(Range("N17").value, "0.00")
Dim commentShapeN17 As Shape
Set commentShapeN17 = Range("N17").AddComment(commentTextN17OrN33).Shape
commentShapeN17.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN17.Height = 300 ' Set the desired height in points
commentShapeN17.Width = 300 ' Set the desired width in points
ElseIf Target.Address = "$B$22" Then
commentTextN17OrN33 = commentTextN17OrN33 & "Each: £" & Format(Range("N33").value, "0.00")
Dim commentShapeN33 As Shape
Set commentShapeN33 = Range("N33").AddComment(commentTextN17OrN33).Shape
commentShapeN33.TextFrame.AutoSize = True ' Allow the text box to adjust size based on content
commentShapeN33.Height = 300 ' Set the desired height in points
commentShapeN33.Width = 300 ' Set the desired width in points
End If

' Create the comment text for cell N6 or N22
Dim commentTextN6OrN22 As String
If Target.Address = "$B$6" Then
commentTextN6OrN22 = "Based on " & Range("B8").value & " a day. "
Dim commentShapeN6 As Shape
On Error Resume Next ' In case N6 doesn't have an existing comment
Set commentShapeN6 = Range("N6").Comment.Shape
On Error GoTo 0
If commentShapeN6 Is Nothing Then
Set commentShapeN6 = Range("N6").AddComment(commentTextN6OrN22).Shape
commentShapeN6.TextFrame.AutoSize = True
commentShapeN6.Height = 25 ' Set the desired height in points
commentShapeN6.Width = 100 ' Set the desired width in points
Else
commentShapeN6.TextFrame.Characters.Text = commentTextN6OrN22
End If
ElseIf Target.Address = "$B$22" Then
commentTextN6OrN22 = "Based on " & Range("B24").value & " a day. "
Dim commentShapeN22 As Shape
On Error Resume Next ' In case N22 doesn't have an existing comment
Set commentShapeN22 = Range("N22").Comment.Shape
On Error GoTo 0
If commentShapeN22 Is Nothing Then
Set commentShapeN22 = Range("N22").AddComment(commentTextN6OrN22).Shape
commentShapeN22.TextFrame.AutoSize = True
commentShapeN22.Height = 25 ' Set the desired height in points
commentShapeN22.Width = 100 ' Set the desired width in points
Else
commentShapeN22.TextFrame.Characters.Text = commentTextN6OrN22
End If
End If
End If
End Sub
 
Upvote 0
Please use the VBA tags when posting code. It makes it easier to read.
1700669585328.png
 
Upvote 0
Here are a few of the ranges

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub    ' this stops code error if more than one cell is changed at once

    If Not Application.Intersect(Target, Me.Range("B6,B22,B38,B54")) Is Nothing Then    ' indicates the Target range
      Range("N" & Target.Row).ClearContents
      Range("N" & Target.Row + 11).ClearContents
    End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Vba extend my range to 25 builds from 2
Replies
1
Views
367
Micron
M
U
  • Question Question
Text Value from Textbox to Integer
Replies
1
Views
362
Kaper
Kaper
M
  • Question Question
Please help me to correct Excel VBA code!
Replies
1
Views
368
Minh Tuan VN
M
A
  • Solved
VBA Help!
Replies
2
Views
430
AusNewb
A
N
  • Solved
sometimes problem with vba (Error 400)
Replies
5
Views
614
Domenic
D

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top