Unmerge Cells with Macro based on value

[Alisan]

Dear friends, I would like to ask you the following;

I need to write a macro which looks up to column C (A dropdown menu, which is selected manually), and if the column C is called "Header", I want that Macro merges the cells between E and W on that specific line.
For this condition, I have the below code which works very fine :

Sub loopandMarge()
Dim lastrow As Long, I As Long
lastrow = Range("C" & Rows.Count).End(xlUp).Row

Application.DisplayAlerts = False
For I = 18 To lastrow
If Cells(I, "C") = "Header" Then
With Range("E" & I & ":W" & I)
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Font.Bold = True
End With
End If
Next I
Application.DisplayAlerts = True
End Sub

However when in column "C" I change the value from the dropdown menu from "Header" to another value, the cells remain merged.
I would like to have a code which can also unmerge the cells, once the value in column "C" change.

Would you be able to help me ?

 

[offthelip]

Using merged cells with VBA causes endless problems, since they are usually only used for display purposes, I recommend using the "format across selection" format instead which usually gives the same appearance.
so try this:

Sub loopandMarge()
Dim lastrow As Long, I As Long
lastrow = Range("C" & Rows.Count).End(xlUp).Row

Application.DisplayAlerts = False
For I = 18 To lastrow
If Cells(I, "C") = "Header" Then
With Range("E" & I & ":W" & I)
.HorizontalAlignment = xlCenterAcrossSelection
.MergeCells = False
.VerticalAlignment = xlCenter
.WrapText = True
.Font.Bold = True
End With
 Else
With Range("E" & I & ":W" & I)
.HorizontalAlignment = xlLeft
.MergeCells = False
.VerticalAlignment = xlCenter
.WrapText = True
.Font.Bold = True
End With
End If

Next I
Application.DisplayAlerts = True
End Sub

 

[Alisan]

Using merged cells with VBA causes endless problems, since they are usually only used for display purposes, I recommend using the "format across selection" format instead which usually gives the same appearance.
so try this:

Sub loopandMarge()
Dim lastrow As Long, I As Long
lastrow = Range("C" & Rows.Count).End(xlUp).Row

Application.DisplayAlerts = False
For I = 18 To lastrow
If Cells(I, "C") = "Header" Then
With Range("E" & I & ":W" & I)
.HorizontalAlignment = xlCenterAcrossSelection
.MergeCells = False
.VerticalAlignment = xlCenter
.WrapText = True
.Font.Bold = True
End With
 Else
With Range("E" & I & ":W" & I)
.HorizontalAlignment = xlLeft
.MergeCells = False
.VerticalAlignment = xlCenter
.WrapText = True
.Font.Bold = True
End With
End If

Next I
Application.DisplayAlerts = True
End Sub

Hello Offthelip,

Thank you very much for your fast reply.Maybe there is a small error in the code, is this possible ? I run the code, however the cells remain merged, when I change the value in column C.
My best regards, ALisan

 

[offthelip]

You need to run the code again after changing the value in col c

 

[Alisan]

You need to run the code again after changing the value in col c

Dear Offthelip, Thank you very much for your help, I am very appreciated. My best regards