Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 172
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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