Run-time error 438 - Object doesn't support this property or method

[Amapola]

Good Afternoon

I'm formatting these large spreadsheets that contain mostly text. I'm trying to find characters used as bullets (Chr(149)) to format them.
The code below gives me a run-time error 438 on this line: If .Characters(i, 1) = SearchValue Then
I have tested the code by changing it to search for characters formatted in a specific colour and that works so the problem must be with trying to find the character. I can't find more specific examples how to find a character buy ASCII code but VBA doesn't like below.

Any pointers would be much appreciated.

Public Sub ColourBullet()

Dim rng As Range, cell As Range
Set rng = ActiveSheet.Range("C10:Q200")
Set cell = ActiveCell

Dim SearchValue As String
SearchValue = Chr(149)

Dim i As Long
Dim StartChar As Long

For Each cell In rng
    With cell
        For i = 1 To Len(.Value)
            If .Characters(i, 1) = SearchValue Then
                .Characters(i, 1).Font.Color = RGB(236, 102, 2)
            End If
        Next i
    End With
Next cell

MsgBox ("Task Completed")
Cells(1, 1).Select

End Sub

Thanks,
Christine

 

[dmt32]

Hi,
try this update to your code & see if does what you want

Public Sub ColourBullet()
    Dim rng As Range, cell As Range
    Dim i As Long
    Dim StartChar As Long
    Dim SearchValue As String
    
    Set rng = ActiveSheet.Range("C10:Q200")
    Set cell = ActiveCell
    
    
    SearchValue = "."
    
    For Each cell In rng.Cells
        With cell
            For i = 1 To Len(.Text)
                If Mid(.Text, i, 1) = SearchValue Then
                    .Characters(i, 1).Font.Color = RGB(236, 102, 2)
                End If
            Next i
         End With
    Next cell
    
    MsgBox ("Task Completed")
    Cells(1, 1).Select

End Sub

Dave

 

[Amapola]

Thanks, Dave. No errors but it doesn't change the Font Color.

Mid(.Text, i, 1) .Font.Color = RGB(236, 102, 2) throws a compile error.

Any further suggestions?

Thanks,
Christine

 

[RoryA]

If the font colour isn't changing, that would suggest that none of the characters actually are what you are looking for.

 

[dmt32]

Thanks, Dave. No errors but it doesn't change the Font Color.

Mid(.Text, i, 1) .Font.Color = RGB(236, 102, 2) throws a compile error.

Any further suggestions?

Thanks,
Christine

Did yo try the whole code I posted unaltered?

Dave

 

[Amapola]

Hi Dave, I take that back. I had a double = SearchValue in the code. Once removed, it works beautifully.

Final code is:

Public Sub ColourBullet()

Dim rng As Range, cell As Range
Dim i As Long
Dim SearchValue As String

SearchValue = Chr(149)
Set rng = ActiveSheet.Range("C10:Q200")
Set cell = ActiveCell

For Each cell In rng.Cells
    With cell
        For i = 1 To Len(.Text)
            If Mid(.Text, i, 1) = SearchValue Then
                .Characters(i, 1).Font.Color = RGB(236, 102, 2)
            End If
        Next i
    End With
Next cell

MsgBox ("Task Completed")
Cells(1, 1).Select

End Sub

 

[dmt32]

Hi Dave, I take that back. I had a double = SearchValue in the code. Once removed, it works beautifully.

Glad resolved - many thanks for feedback

Dave