Bold/Italic only the variable name within a cell's value

check out http:Q_20489190.html... (example for you shown below):


leave your cell with this:
="It is our company’s objective that " & Course_Name & "'s fleet will receive professional, timely and systematic service."

but then put this code in the code module for whichever worksheet this applies to (press Alt+F11, double-click on the worksheet in the window on the left):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim searchForThis As String: searchForThis = "Course_Name"
    If InStr(1, Target.Formula, searchForThis) = 0 Then Exit Sub
    Target.Characters(InStr(1, Target.Formula, searchForThis), Len(searchForThis)).Font.Bold = True
End Sub

hope that helps!
-PtG

wow, it took me at least 10 mins to do that? shoot... i gotta work on that!

Regards,
PtG

OK - I can't seem to get either of these solutions to work.  I've been playing with PtG's mostly because I "think" I understand that one better.

Here are some other caveats that I should perhaps mention;
1. The named field "Course_Name" is not on this same sheet but on another sheet within the same workbook.
2. The reference to "&Course_Name&" appears multiple times on this page where I want the bold/italicized text to appear
3. Not only does the reference to "&Course_Name&" appear in multiple cells, it can appear multiple times in any cell.

I have the following code in the worksheets "Change" section.  Is that correct?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)    
    Dim searchForThis As String: searchForThis = "Course_Name"
    If InStr(1, Target.Formula, searchForThis) = 0 Then Exit Sub
    Target.Characters(InStr(1, Target.Formula, searchForThis), Len(searchForThis)).Font.Bold = True
    Target.Characters(InStr(1, Target.Formula, searchForThis), Len(searchForThis)).Font.Italic = True

End Sub

try this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)    
    Dim searchForThis As String: searchForThis = Range(Names("Course_Name").Value)
    t1 = 0
    While InStr(t1 + 1, Target.Formula, searchForThis) > 0
        t1 = InStr(t1 + 1, Target.Formula, searchForThis)
        Target.Characters(InStr(1, Target.Formula, searchForThis), Len(searchForThis)).Font.Bold = True
        Target.Characters(InStr(1, Target.Formula, searchForThis), Len(searchForThis)).Font.Italic = True
    Wend
End Sub

hope that helps!
-PtG

P.S. - yes, put it in the Worksheet_Change event in the module whichever worksheet you want it to trigger on a change in... did that make sense? :-P

Sorry I can't seem go that to work either.  :-(  I found out that it did not like the reference to Course_Name when setting the searchForThis value.  Perhaps because its from another sheet?  To get by it, I set J2 cells value on this worksheet to = Course_Name from the other work sheet. That allowed me to at least run the macro without bombing out.  

So here's how it looks now in the change event of Sheet12;

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

   'Dim searchForThis As String: searchForThis = Range(Names("Course_Name").Value)
    Dim searchForThis As String: searchForThis = Range("J2").Value
    t1 = 0
    While InStr(t1 + 1, Target.Formula, searchForThis) > 0
         t1 = InStr(t1 + 1, Target.Formula, searchForThis)
        Target.Characters(InStr(1, Target.Formula, searchForThis), Len(searchForThis)).Font.Bold = True
        Target.Characters(InStr(1, Target.Formula, searchForThis), Len(searchForThis)).Font.Italic = True
    Wend

End Sub

Any suggestions?

I figured out what it was... I forgot to put in "Application.", so it was referring simply to the ranges/named ranges on that worksheet... here you go, enjoy!

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim searchForThis As String: searchForThis = Application.Range(Application.Names("Course_Name").Value)
    t1 = 0
    While InStr(t1 + 1, Target.Formula, searchForThis) > 0
        t1 = InStr(t1 + 1, Target.Formula, searchForThis)
        Target.Characters(InStr(1, Target.Formula, searchForThis), Len(searchForThis)).Font.Bold = True
        Target.Characters(InStr(1, Target.Formula, searchForThis), Len(searchForThis)).Font.Italic = True
    Wend
End Sub

Regards,
PtG

This is not working either for several reasons.  
1. Target.Formula.  This is targeting the cell that the change is made in, what I need it to do is reference other cell(s).  (Will deal with that issue later)
2. Searching for searchForThis in Target.Formula doesn't work.  You either have to search for "&Course_Name&" or searchForthis in Target.Text
3. t1 creates an endless loop. I added the t2 test
4. It still does not bold or italicize the needed text.  The Target.Characters statements "look" ok to me - but obviously something is wrong since it is not achieving the desired results

Anyways I thought if I could at least get one targetted cell to work for just one occurance, then I could make changes for other cells and multiple occurances.  But I'm not having any luck.  This is where I am at now.  

Dim searchForThis As String: searchForThis = Application.Range(Application.Names("Course_Name").Value)
    t1 = 0
    t2 = 0
    'While InStr(t1 + 1, Target.Formula, searchForThis) > 0 ; Commented out and replaced with next line
     While InStr(t2 + 1, Target.Formula, "&Course_Name&") > 0
        't1 = InStr(t1 + 1, Target.Formula, searchForThis) ; Commented out and replaced with next line
        t1 = InStr(t2 + 1, Target.Text, searchForThis)
        Target.Characters(InStr(Target.Text, searchForThis), Len(searchForThis)).Font.Bold = True
        Target.Characters(InStr(1, Target.Text, searchForThis), Len(searchForThis)).Font.Italic = True
        t2 = t2 + Len(searchForThis)
    Wend

The theory is; I have a "Configuration Sheet" with data on it.  One of which is Course_Name.  I have another sheet called Special_Options.  Anytime Course_Name gets changed on the one sheet, I would like all text occurances on the Special_Options page to be bold and highlighted.  

I'm still open to suggestions

I've tried a couple things with this... and I found out that you can only change bold and italic for specific characters in a cell that ***doesn't have a formula.*** This is rather unfortunate, since that's kinda the whole point for you. However, I do have a suggestion:

If you have a string variable that stores the current value of Course_Name, then when Course_Name is changed you can search through and replace all occurrances of the previous value of Course_Name with the new value, applying bold/italic at the same time. In order for this to work, you'll have to change all instances of "&Course_Name&" with the actual value for Course_Name, and remove the equals sign (so basically make it regular text).

Note: there still *might* be a possibility of searching through the formula and being able to bold/italicize the text... don't count on it. I'll try to figure something out this weekend, but I'm really busy today, I have a lot to do at work, so I can't post more than one or two more posts today (maybe tonight I can).

Code for my suggestion:
==================

'place the following code in the ThisWorkbook module:

Dim myCurrentCourseName As String

Private Sub Workbook_Open()
    myCurrentCourseName = Application.Range(Application.Names("Course_Name").Value)
End Sub

'place the following code in the "Configuration Sheet" module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Address <> Application.Names("Course_Name").RefersToRange.Address Then Exit Sub
    Dim searchForThis As String: searchForThis = ThisWorkbook.myCurrentCourseName
    Dim replaceWithThis As String: replaceWithThis = Application.Range(Application.Names("Course_Name").Value)
    Dim c1 As Range
    For Each c1 In ThisWorkbook.Worksheets("Special_Options").UsedRange.Cells
        t1 = 0
        While InStr(t1 + 1, c1, searchForThis) > 0
            t1 = InStr(t1 + 1, c1, searchForThis)
            c1 = Left(c1.Text, t1 - 1) & replaceWithThis & Mid(c1.Text, t1 + Len(searchForThis))
            c1.Characters(InStr(1, c1, replaceWithThis), Len(replaceWithThis)).Font.Bold = True
            c1.Characters(InStr(1, c1, replaceWithThis), Len(replaceWithThis)).Font.Italic = True
        Wend
    Next
    ThisWorkbook.myCurrentCourseName = replaceWithThis
End Sub

=============
after you put the code into your workbook and make the necessary changes, you will have to save and reload the workbook (so it activates the Workbook_Open event) and then everything should work fine (works just fine for me).

hope that helps!
-PtG

You are going to hate me and be sorry that you ever answered this post (LOL)
BUT---We ARE getting closer!  
I had to change the "Dim myCurrentCourseName As String" to "Public myCurrentCourseName As String"
I realize this will apply to all open workbooks, but thats the only way I got it to work.

Then the code seems to work on the FIRST occurance of the course name within a cell.  It does replace all occurances of the course name, but it does not bold/italic them all, just the first one within a given cell.  It does work on all cells on the sheet.  So now we have to tackle the problem that within a given cell, course name may be referenced more than once - and make them all bold/italic.

Then I have a  concern of what happens if the user changes the course name to a blank field.    Actually I know, its not a problem when they blank the field, big problem when they re-enter a value.  Is there a way to prevent the course name value from ever being a null but can still have any value?  I've tried the validation and using text length, but it still allows me to blank the cell (even though I left the Ingore Blank unchecked)

No rush on this Paul - what you've done so far has been super!  I REALLY do appreciate the input!

Never mind on then preventing blank Course Name.
I added the following line before the Dim stmts in Configuration Sheet module.
   If Target.Value = "" Then Exit Sub

Guess that lunch break helped....

lol btw, you might want to try something like this:

If Len(Target.Value) < 2

just in case the user types in a space to clear it or something like that...

yes, my bad, I mean to make that Public... in fact, I made it Public in mine when I was testing it, but forgot to re-paste that code in there :-)

hey, I'm gonna be really busy the rest of the day, I'll try to post tomorrow. And don't worry about it, I enjoy doing this kinda stuff... just wish I had more time to do it!

Regards,
PtG

Hi Paul,
Now that I've finished with my month end processes I can get back into designing - so continuing where we left off...

Believe it or not, I've actually caught people typing in spaces equal to the previous entry in order to clear it.  Amazing what people think of!  So I'm including this line as well;
If Left(Target.Value, 1) = " " Then Exit Sub

Any  more thought on how to replace multiple occurances in one cell?  I consider myself pretty good at this stuff, but I admit I'm a little lost in the for each with the while loops you've provided.  So Here's where I'm at.  Any thing else you can add is appreciated.
Thanks!
Sharon


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Address <> Application.Names("Course_Name").RefersToRange.Address Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If Len(Target.Value) < 2 Then Exit Sub
    If Left(Target.Value, 1) = " " Then Exit Sub
    Dim searchForThis As String: searchForThis = ThisWorkbook.myCurrentCourseName
    Dim replaceWithThis As String: replaceWithThis = Application.Range(Application.Names("Course_Name").Value)
    Dim c1 As Range
    Sheets("Special_Options").Unprotect
    For Each c1 In ThisWorkbook.Worksheets("Special_Options").UsedRange.Cells
        t1 = 0
        While InStr(t1 + 1, c1, searchForThis) > 0
            t1 = InStr(t1 + 1, c1, searchForThis)
            c1 = Left(c1.Text, t1 - 1) & replaceWithThis & Mid(c1.Text, t1 + Len(searchForThis))
            c1.Characters(InStr(1, c1, replaceWithThis), Len(replaceWithThis)).Font.Bold = True
            c1.Characters(InStr(1, c1, replaceWithThis), Len(replaceWithThis)).Font.Italic = True
        Wend
    Next
    Sheets("Special_Options").Protect
    ThisWorkbook.myCurrentCourseName = replaceWithThis
    Range("B4").Value = replaceWithThis
End Sub

Attn Moderato: This question has not been answered completely, but has been of benefit.  Can I award 300  partial points to PaultheGreat?

slindeen,
if you still haven't solved this problem and if you want i can try to make time at some point to take another look at it... i just always seem to be so busy here, my first term in college... i haven't made a real post for almost a month! so let me know...

Regards,
PtG