CODEWITHSUNDEEP
vbaexcel
Vanegh
Vanegh

Reputation: 15

Merge a cell in excel, depending on it's value

I want to merge a cell automatically with the one underneath if the cell has a certain value ("vv"). a solution i found is to check every cell in an array every time a change is made, but thought there would be a possibility to check the value of a cell after it changed?

So if I enter in a blank cell "vv" (without quotes) and I select a different cell I'd like that cell (with vv in it) to merge with the one right under it. in my solution with the array it takes a second every time you change a cell, which is not neat if you make a lot of changes. Any help?

Upvotes: 1

Views: 7053

Answers (1)

Peter Albert
Peter Albert

Reputation: 17475

Try this code in your worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value = "vv" Then Target.Resize(2).Merge
End Sub

In case you want to prevent any content in the cell below, this code will ask you if the cells shall be merged in case any content is found:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value = "vv" Then 
        If Target.Offset(1).Value  "" Then
             If MsgBox("Do you want to overwrite the cell below (containing '" & _
                 Target.Offset(1) & "?", vbYesNo) = vbYes Then
                 Target.Resize(2).Merge
            End If
        Else
            Target.Resize(2).Merge
        End If
    End If
End Sub

Note: The code needs to go in the target sheet, not a new module, as it is an Event procedure:

code placement

Upvotes: 4

Related Questions