• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 116
  • Last Modified:

Excel VBA - Highlight specific characters in a string

I have this in a cell formatted Wingdings2 (example)
Activecell.Value = Chr(188) & Chr(189) & Chr(154) & Chr(152)

Edit: It goes that as a UDF (   =GetAttenanceString("C10") for example  )
It works OK when the string is actually entered in the cell. Mystery!


Where the character is Chr(154) I want to color it Red. I'm doing it like this. BUT, although the If condition seems to work the entire string is being colored Red, rather than just the Chr(154). What am I missing here please?

Sub BlankNonAttendingDays()

    For iDay = 1 To 7
        If Mid(ActiveCell.Value, iDay, 1) = Chr(154) Then
            'MsgBox iDay
        
            With ActiveCell.Characters(Start:=iDay, Length:=1).Font
                .Size = 11
                .Color = 192
            End With
        End If

    Next iDay

End Sub

Open in new window

0
hindersaliva
Asked:
hindersaliva
  • 2
  • 2
2 Solutions
 
Roy CoxGroup Finance ManagerCommented:
Why are using ActiveCell? An example workbook would help
0
 
hindersalivaAuthor Commented:
ActiveCell for testing Roy. Yes I'll post a workbook later today.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You cannot format the part of the text returned by the formula.
Copy and paste the value in another cell and the same piece of code would work without an issue with the cell with value only.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Or you should try something like this.....
Sub BlankNonAttendingDays()
ActiveCell.Value = ActiveCell.Value 'This line will remove the formula and place the value
    For iDay = 1 To 7
        If Mid(ActiveCell.Value, iDay, 1) = Chr(154) Then
            With ActiveCell.Characters(Start:=iDay, Length:=1).Font
                .Size = 11
                .Color = 192
            End With
        End If
    Next iDay
End Sub

Open in new window

1
 
Roy CoxGroup Finance ManagerCommented:
Pleased to help
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now