Merging cells and changing colour

[markh9999]

I need some code that looks at a range of cells and if the string "abcd" appears somewhere in the cell text then I need to merge it with the cell below and change the background colour.

Can someone start me off please?

 

[VoG]

Try like this

Sub mrg()
Dim c As Range
Application.DisplayAlerts = False
For Each c In Range("A1:D10")
    If c.Value Like "*abcd*" Then
        c.Interior.ColorIndex = 6
        c.Resize(2).Merge
    End If
Next c
Application.DisplayAlerts = True
End Sub

 

[markh9999]

Thank you Peter - so simple when done by an expert !!

Also, how do I now combine the text from the original 2 cells to form the text for the merged cells?

 

[VoG]

Try

Sub mrg()
Dim c As Range
Application.DisplayAlerts = False
For Each c In Range("A1:D10")
    If c.Value Like "*abcd*" Then
        c.Value = c.Value & Chr(10) & c.Offset(1).Value
        c.Interior.ColorIndex = 6
        With c.Resize(2)
            .Merge
            .WrapText = True
        End With
    End If
Next c
Application.DisplayAlerts = True
End Sub

 

[markh9999]

Thank you again ... and one final (hopefuly) question:

I'm struggling with the syntax on the following.

Once I've merged the cells using the code below, I want to find and replace some repeated text within the merged cell.

e.g the text *abc* occurs 5 times (* is wildcard) and needs removing.

Can you help?

 

[VoG]

Try

Sub mrg()
Dim c As Range
Application.DisplayAlerts = False
For Each c In Range("A1:D10")
    If c.Value Like "*abcd*" Then
        c.Value = c.Value & Chr(10) & c.Offset(1).Value
        c.Interior.ColorIndex = 6
        With c.Resize(2)
            .Merge
            .WrapText = True
        End With
        c.Value = Replace(c.Value, "abc", vbNullString)
    End If
Next c
Application.DisplayAlerts = True
End Sub