How can I modify (change to bold) a specific string of text in a merged cell in Microsoft Excel VBA?

Question

I can successfully modify a single or group of normal cells with the following input with the following:

.Range("C" & i) _
.Characters(WorksheetFunction.Find("NOTE:", Range("C" & i).Value, 1), 100) _
.Font.Bold = True

which runs down the length of the C column and bolds everything after "NOTE:" (specifically 100 characters past and including the found text string)

The problem is - if there are any merged cells in the C column it ignores it. I've run some quick tests - it recognizes there is text in the cell, and I can print the contents of the merged cell into a single cell that can later find the text string, but won't find "NOTE:" for some reason in the merged cell itself

You may find [Count keywords within phrases](http://stackoverflow.com/questions/32860792/count-keywords-within-phrases) of interest. [sample image](http://i.stack.imgur.com/RAAM7.png) — , Mar 22 '16 at 21:10

score 3 · Accepted Answer · answered Mar 22 '16 at 17:06

3

Just take the first (top left) cell from the merged range and continue with your code:

With .Range("C" & i).MergeArea.Cells(1, 1)
    lngFound = InStr(1, .Value2, "NOTE:", vbTextCompare)
    If lngFound > 0 Then
        .Characters(lngFound, 100).Font.Bold = True
    End If
End With

answered Mar 22 '16 at 17:06

Ralph

9,284
4
32
42

this worked beautifully! I just gotta figure out what you did now and why it works... thanks a bunch! – jlandy Mar 22 '16 at 17:47
Instead of `.Range("C" & i)` I used `.Range("C" & i).MergeArea.Cells(1, 1)` that's actually all. The rest I just changed for performance. And the above change is merely (written out): if `.Range("C" & i)` is part of a [merged area](https://msdn.microsoft.com/en-us/library/office/ff822300.aspx) then give me the entire area and of that area I just want the `.Cells(1,1)` top left cell. – Ralph Mar 22 '16 at 17:56

TheGuyThatDoesn'tKnowMuch · Answer 2 · 2016-03-22T17:06:39.897

From what I can tell, it works for merged cells that begin with the cell in Column C, but breaks when the merged cells begin before Column C. I was able to get it to work using an If statement to determine if the value in Range("C" & I) was blank, then select the first cell of the merged cells and perform the formatting.

See below for the code I got to work. My dataset was only for Cells C1:C20:

Sub test()

Dim i As Integer
Dim rng As Range

With ThisWorkbook.ActiveSheet
On Error Resume Next

For i = 1 To 20

If .Range("C" & i).Value = "" Then

    If Range("A" & i).Value <> "" Then

        Set rng = Range("A" & i)

    Else

        Set rng = .Rows(i).SpecialCells(xlCellTypeBlanks)(1).Offset(0, 1)

    End If

rng.Characters(WorksheetFunction.Find("NOTE:", rng.Value, 1), 100).Font.Bold = True

Else

    .Range("C" & i).Characters(WorksheetFunction.Find("NOTE:", Range("C" & i).Value, 1), 100).Font.Bold = True

End If

Next i

End With

End Sub

Before (borders used to represent where merged cells start and end):

After:

How can I modify (change to bold) a specific string of text in a merged cell in Microsoft Excel VBA?

2 Answers2