Automatically merge cells when a certain value is detected

[CBWiggle]

I'm trying to create a code that will make a selection of cells in a row automatically merge when a value in column A appears.

If the value "Group 1" or "Group 2" appears in cell A2, the range A2:N2 should merge. I'd need to use this code in other rows of the spreadsheet too if possible, and if the value in column A is changed again, the cells should automatically return to being separate (not merged).

Is this possible?

I'm very new to VBA, so thanks for your help!
Charlotte

 

[oldbrewer]

Re: Automatically merge cells when a certain value is detected [ Please help ]

do they have to merge - it would be easy to color A2:N2 red if A2 = group1 or group2

and if you changed it to group 3 the red color would disappear

 

[CBWiggle]

Re: Automatically merge cells when a certain value is detected [ Please help ]

They don't have to merge i suppose!

With that in mind, i just tried to make a code for it, but it doesn't seem to be working :/

Could you help me figure out what i need to change in this please?

Sub Macro1()
'
' Macro1 Macro
'
Dim group As String
Set group.Value = Range("A2").Value


If group.Value = "Group 1" Or group.Value = "Group 2" Then


'    Range("A2:N2").Select
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Range("A2").Select
End Sub

Thanks
Charlotte

 

[oldbrewer]

Re: Automatically merge cells when a certain value is detected [ Please help ]

I cannot see where in your code you are merging cells

Range("A1:E1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
End Sub

this code merges A1:A5