changing font format via vb

[asylum]

Hi,

I have a sheet with lot of data in range a1: t100 all of it is text, there are no formulas in here.

Sprinkled throughout the data there are a couple of hundred instances of an exclamation mark !.

I need to turn all of these into bold format, they may appear anywhere in the text. I anticipate it being something like:

Sub bold()

Dim boldrng as range

set boldrange = (a1,t100)

for each cell in range find "!" and when found then:

With ActiveCell.Characters(!).Font
.FontStyle = "Bold"

End With

End Sub

any thoughts?

Thanks

Andy

 

[njimack]

Sub Bold()
Dim r As Range
Dim l As Integer

Application.ScreenUpdating = False

For Each r In Range("A1:T100")
    If InStr(1, r.Value, "!", 1) Then
        l = WorksheetFunction.Find("!", r.Value)
        r.Characters(Start:=l, Length:=1).Font.Bold = True
    End If
Next r

Application.ScreenUpdating = True

End Sub

 

[asylum]

HI Neil,

Thats great thanks works a treat... but.... (i should have mentioned this), sometimes cell contain more than one !, such as

apples! oranges bananas!

and this only find an changes the first instance, how woudl we modify it to get all instances?

Thanks

Andy

 

[njimack]

A bit messy, and may take a while if your cells contain hundreds of characters...

Sub Bold2()
Dim r As Range
Dim i As Integer

Application.ScreenUpdating = False

For Each r In Range("A1:T100")
    If InStr(1, r.Value, "!", 1) Then
        For i = 1 To Len(r.Value)
            If r.Characters(Start:=i, Length:=1).Text = "!" Then _
            r.Characters(Start:=i, Length:=1).Font.Bold = True
        Next i
    End If
Next r
            
Application.ScreenUpdating = True

End Sub

 

[asylum]

Fantastic, thnk you very much