CODEWITHSUNDEEP
excelvba
Sylca
Sylca

Reputation: 2545

Merge some cells depending on certain cell value

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.

Upvotes: 2

Views: 2026

Answers (1)

brettdj
brettdj

Reputation: 55682

  • 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

Upvotes: 2

Related Questions