VBA Merge Help

Level II Help.jpg
Good Day! I am active duty military that runs construction projects for the Navy. I am currently in the process of automating a template while teaching myself VBA at the same time. The file I'm currently working lists my Project's Master Activities on the left and tracks them via 3 rows that have time estimated, time earned, and time expended. Those Columns fall under a bi-weekly reporting periods. Row 6 is the full date (5/9/2023 format) but uses the "ddd" display to show just the day. Rows 4 and 5 are =TEXT(G$6,"mmmm") and supposed to merge together to display the month. The goal is that the user can change G6s date, that trickles down the columns with a +14 days for the bi-weekly reporting. (that already works) then the VBA automatically unmerges G4:AF5 (a full year), autofills the range with =TEXT(G$6,"mmmm"), then it runs a merge code that should merge all similar valued cells together. However, where the code currently sits it only merges similar cells across rows 4 and 5, but does not merge G4:G5, or J4:L5 for November for example. I have tried running the merge code a second time but that runs into errors once added. Any help with this would be very much appreciated!
Code:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

If Target.Address = "$G$6" Then

Range("G4:AF5").Select
Selection.UnMerge
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Range("G4").Select
Selection.AutoFill Destination:=Range("G4:AF4"), Type:=xlFillDefault
Range("G4:AF4").Select
Range("G5").Select
Selection.AutoFill Destination:=Range("G5:AF5"), Type:=xlFillDefault
Range("G5:AF5").Select
End If

Dim rng As Range, cell As Range, mergedRange As Range
Set rng = Range("G4:AF5")

For Each cell In rng
If cell.Value <> "" And cell.Value = cell.Offset(0, 1).Value Then
If mergedRange Is Nothing Then
Set mergedRange = cell
End If
Set mergedRange = Union(mergedRange, cell.Offset(0, 1))
Else
If Not mergedRange Is Nothing Then
mergedRange.Merge
mergedRange.HorizontalAlignment = xlCenter
Set mergedRange = Nothing
End If
End If
Next cell

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

The simple answer is 'do not use merged cells' - browse the web for reasons not - they have no place on an Excel app - especially if you are using VBA to expedite actions.

You can try centre across selection as an alternative to merging:

1, Select the range of cells over which you want to center text.
2, Right-click the selected cells, and then click Format Cells.
3, Click the Alignment tab.
4, In the Horizontal list, click Center Across Selection.
5, Click OK.

https://exceljet.net/videos/how-to-u...ction-in-excel

If you add or remove columns from the range that was centred then it will still centre across the range unless you delete the cell that has the month name in.

The idea is that you place your value in the leftmost cell, then centre across selection, even though the value moves - it is still in the first leftmost cell. If you need to change the month then you can select the leftmost cell and edit the value, when you exit that cell it will go back to the centre.