1

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?

bonCodigo
  • 14,268
  • 1
  • 48
  • 91
Vanegh
  • 15
  • 1
  • 4
  • 1
    What if you have any value in the cell right underneath the cell you are typing? e.g. B2 you are typing `vv`. Then B3 has some other value. Or you don't care about it at all? – bonCodigo Feb 01 '13 at 13:13
  • @bonCodigo: Good point. I amended the answer to ask the user. – Peter Albert Feb 01 '13 at 14:08

1 Answers1

4

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

Peter Albert
  • 16,917
  • 5
  • 64
  • 88
  • sorry - i forgot to mention that i found a solution to check the value under the cell, but thanks very much. It works as i intended – Vanegh Feb 04 '13 at 09:56
  • You're welcome. Please mark the answer as solved then so the question gets closed! – Peter Albert Feb 04 '13 at 10:09