Solved

Excel VBA - Highlight specific characters in a string

Posted on 2016-11-05
5
52 Views
Last Modified: 2016-11-17
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
Comment
Question by:hindersaliva
  • 2
  • 2
5 Comments
 
LVL 17

Assisted Solution

by:Roy_Cox
Roy_Cox earned 250 total points
ID: 41875338
Why are using ActiveCell? An example workbook would help
0
 

Author Comment

by:hindersaliva
ID: 41875345
ActiveCell for testing Roy. Yes I'll post a workbook later today.
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41875365
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
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 250 total points
ID: 41875368
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
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41892506
Pleased to help
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Tags from access to excel 3 28
sql server query from excel 3 57
Excel Hyperlink Question 4 30
how to add fields to my existing script 7 12
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now