Macro to merge cells

[Russk68]

Hi all
I would like to enter a value in a cell and have the 2 cells above and the 2 cells below all merge and increase the font size to 24 in bold.

Example:
A value is entered in A3.
A1:A5 is merged.
Font size increases to 24 in bold.

Possible?

Thank you!

Russ

 

[Russk68]

Oh!
And also undo when a value is removed.

 

[Michael M]

In the sheet module try

Private Sub Worksheet_change(ByVal target As Range)
If Range("A3").Value <> "" Then
    With Range("A1:A5")
        .MergeCells = True
        .Font.Size = 24
    End With
Else
    With Range("A1:A5")
    .Font.Size = 11
    .MergeCells = False
    End With
End If
End Sub

 

[Russk68]

That's cool!
Would it be possible for it to work anywhere in column A and more than once?

 

[Michael M]

Maybe this

Private Sub Worksheet_change(ByVal target As Range)
If Intersect(target, Range("A:A")) Is Nothing Then Exit Sub
If target.Cells(1).Value <> "" Then
    With Range(Cells(target.Row - 2, 1), Cells(target.Row + 2, 1))
        .MergeCells = True
        .Font.Size = 24
    End With
Else
    With target.Cells(1)
    .Font.Size = 11
    .MergeCells = False
    End With
End If
End Sub

 

[Russk68]

So close!
If I enter a value in A3 and then delete it, the cells unmerge and the target cell becomes A1. A2:A4 do not return to the font size.

In the Else code, can you include the 4 rows below the target cell?

Thank you!

 

[Michael M]

This then....

Private Sub Worksheet_change(ByVal target As Range)
If Intersect(target, Range("A:A")) Is Nothing Then Exit Sub
If target.Cells(1).Value <> "" Then
    With Range(Cells(target.Row - 2, 1), Cells(target.Row + 2, 1))
        .MergeCells = True
        .Font.Size = 24
    End With
Else
    target.Cells(1).UnMerge
    Range(Cells(target.Row, 1), Cells(target.Row + 4, 1)).Font.Size = 11
End If
End Sub

 

[Russk68]

That works!

This is very cool

Thank you very much Michael!

 

[Michael M]

Glad to help...

 

[Doflamingo]

Hello @Michael M,

I've seen the code you proposed, would you know how to adapt it to the values of a listbox ?

Here is the code that displays all the items of a listbox in the column B of the sheet 9 of an excel spreadsheet

For b = 0 To ListBox1.ListCount - 1
With Cells(b + 3, 2)
        .ColumnWidth = 15
        .Value = ListBox1.List(b)
End With
Next

And what I would like to do in the column A is to make appear the value of a textbox in merge cells in function of the number of items of the listbox 1 that are displayed in the column B

Any ideas ?