Vba if cell = something then merge another range of cells

[Josiah]

If any cell in column B = 70 (actually, B18:B61), then merge the current row's cells in columns J, K and L

IE:
If Cell B40 = 70 then merge J40:L40
If Cell B50 = 70 then merge J50:L50
And so on...


How should I write this?

TIA!

 

[bob33]

I'm fuzzy on what is meant by merging the range? What happens to all the values in that range? Are they globbed together or is the first entered, etc....?

 

[mumps]

Try:

Sub MergeCells()
    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In Range("B18:B61")
        If rng = "70" Then
            Range("J" & rng.Row & ":L" & rng.Row).Merge
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[Josiah]

I'm fuzzy on what is meant by merging the range? What happens to all the values in that range? Are they globbed together or is the first entered, etc....?

The range wouldn't contain any data before merging.



ADDITION:

I also want the current row's range to UNMERGE if the cell that originally contains 70 is deleted.

IE:
If Cell B40 = 70 then merge J40:L40
If Cell B40 = "" then UNMERGE J40:L40 ("" = 70 is deleted)

 

[Josiah]

Trying your solution now mumps! I replied before I saw what you wrote. I'm sure the unmerge process will be simple

 

[Josiah]

Hm...I wasn't sure it mattered but I'm actually adding this as a private sub Worksheet_change (if that's the right terminology)

When I try it I get a NEXT WITHOUT FOR error...

 

[Josiah]

Nevermind! Works perfectly!! Thanks mumps

 

[mumps]

My pleasure.