Merge some cells depending on certain cell value

2
votes

So, I would like to merge some cells and I would like to do it like this:

enter image description here

In the picture above I've presented the kind of merge that I would like to have. So, if I enter some text in cell B6 I'd like to cell range C6:F9 and G6:K9 to be merged. If B10 than C10:F13 and G10:K13. and so on ...

Also, text in these merged cells should be wrapped.

When I enter code I'll have automatically generated text in this merged cells. That text will be large and it needs to be wrapped.

I've tried a lot of things with formulas, looked for something that could help me. I know that with code I could do this but I'm not into VBA programming/code/...!

If there is someone that could help me, any help will be appreciated.

1
excelvba
Don't you mind sharing with us at least a relevant piece of your efforts - just in support of What have you tried? Macro-recorder for all the manual steps you describe will produce relevant code. Also you'll need Worksheet Change event to track changes in cells and update others automatically. – Peter L.

1 Answers

2
votes
  • right-click your sheet tab
  • View Code
  • copy and paste in the code below

This code will run the merge only for every 4th cell changed in column B, starting from B2

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Intersect(Target, Range("B:B"))
If rng1 Is Nothing Then Exit Sub
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
For Each rng2 In rng1
'If rng2.Row > 1 Then
If (rng2.Row - 2) Mod 4 = 0 Then
With rng2.Offset(0, 1).Resize(4, 4).Cells
.MergeCells = True
.WrapText = True
End With
End If
'End If
Next
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub