Formatting specific text within a cell.

chipgiii

Board Regular
Joined
Nov 24, 2008
Messages
136
My spreadsheet contains in column F project descriptions. In any given cell in column F there can be text that reads: "No Description Provided," or "Prior Description," or "New Description"
I would like to have all of the quoted instances above show up in bold while the remaining characters stay as is. Is this possible?
Example:
Prior Description: Additional 3000 m2 of new, high quality glasshouses for high throughput ABC to support ABC activities in ABC. New Description: What’s driving it:ABC. Please check with FRank. We are suspending planning for ABCC at this point.
What it accomplish: n.a.
Why that’s important: n.a.



<tbody>
</tbody>
 
Last edited:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
This should do the trick:
Code:
'r is the text to be searched.'rHighlights is the list of words to be highlighted.


Sub Test()


    Dim r As Range
    
    Set r = Sheet1.Range("A1:A3")
    
    HighlightText r


End Sub


Public Sub HighlightText(rng As Range)


    Dim rCell As Range
    Dim rHL As Range
    Dim rHighlights As Range
    Dim lPlace As Long
    
    Set rHighlights = Sheet2.Range("A1:A3")
    
    For Each rCell In rng
        For Each rHL In rHighlights
            lPlace = InStr(rCell, rHL)
            If lPlace > 0 Then
                rCell.Characters(Start:=lPlace, Length:=Len(rHL)).Font.FontStyle = "Bold"
            End If
        Next rHL
    Next rCell


End Sub
 
Upvote 0
I don't know what I am doing wrong but I can't get it to work. I even opened a new book so that I could put the list on Sheet 1 and data on Sheet 2 - still not working.
 
Upvote 0
Try this, I've used the sheet tab names rather than the sheet code names here:

Code:
Sub Test()


    Dim r As Range
    
    Set r = ThisWorkbook.Worksheets("Sheet1").Range("A1:A3")
    
    HighlightText r


End Sub




Public Sub HighlightText(rng As Range)


    Dim rCell As Range
    Dim rHL As Range
    Dim rHighlights As Range
    Dim lPlace As Long
    
    Set rHighlights = ThisWorkbook.Worksheets("Sheet2").Range("A1:A3")
    
    For Each rCell In rng
        For Each rHL In rHighlights
            lPlace = InStr(rCell, rHL)
            If lPlace > 0 Then
                rCell.Characters(Start:=lPlace, Length:=Len(rHL)).Font.FontStyle = "Bold"
            End If
        Next rHL
    Next rCell


End Sub
 
Upvote 0
Hi Darren, The code you created runs without error, so I am inclined to believe Mr Clueless about VBA, me, is missing something. My data is on Sheet2(Data) and I have placed the three selections on Sheet1. I have also placed them on the same sheet "Sheet2(Data) but in CX1:CX3. The Project descriptions are on Sheet2(Data) in column F.

I am not sure what info I am not providing you??? I highlight column F and then run the code, but nothing changes?

I really appreciate your help.
 
Upvote 0
Try this code. It will run on whichever cells you have selected (Set r = Selection) and will search for the words you place on the worksheet called 'Data' in cells CX1:CX3. It will only highlight the first occurrence of a word though.

Code:
Sub Test()

    Dim r As Range
    
    Set r = Selection  'This will execute on whichever cells you have selected.
    
    HighlightText r


End Sub


Public Sub HighlightText(rng As Range)


    Dim rCell As Range
    Dim rHL As Range
    Dim rHighlights As Range
    Dim lPlace As Long
    
    'Set rHighlights = ThisWorkbook.Worksheets("Sheet1").Range("A1:A3")
    Set rHighlights = ThisWorkbook.Worksheets("Data").Range("CX1:CX3")
    
    For Each rCell In rng
        For Each rHL In rHighlights
            lPlace = InStr(rCell, rHL)
            If lPlace > 0 Then
                rCell.Characters(Start:=lPlace, Length:=Len(rHL)).Font.FontStyle = "Bold"
            End If
        Next rHL
    Next rCell


End Sub
 
Upvote 0
I don't think my request is possible. This definitely changes to bold but it changes the entire cell contents to bold. I was hoping just to the instances of:

No Description Provided
Prior Description
New Description

changed to bold with the rest of the cell's text remaining as is.

Sorry if I am frustrating you, but it also turns any cell selected to bold regardless of whether they contain the words in CX1:CX3 or not.
 
Upvote 0
Which version of Excel are you using? It's working in 2003 & 2010 - only highlights the words contained in cells CX1:CX3 and leaves the rest of the text in the cell as what it was.

Can you post the exact code you're using if it's any different from mine.
 
Upvote 0
OPppps now is working perfectly! I am using 2010. I re copied it in and it works flawlessly. Not sure what I did a few minutes ago. You have no idea how terrific this is or how impressed I am. I am a long time user who loves formulas, especially complex array ones, but VBA has always been something I've avoided. I am pretty good in excel, and guys like you make me feel like a novice. Sheesh after starting with Lotus 123 in 1989 and writing macros beginning with the slash..... Then Quattro Pro, and shortly after that in the early 90's Excel. Anyway one last question:

I am considering Office 365 (I use a company laptop with two monitors and docking station setup duplicated here at home. I am frustrated that we are still using 2010 and 2013 has been out for a while. I've been reading the pros and cons but the idea seems like the way everything is going. Have you used 2013 and if so do you like it?

BTW, I can't thank you enough for this macro. It is beautiful.
 
Upvote 0
Yep, long time user to - started with Excel 97, but the first program I wrote was in Sinclair Basic on the ZX-81, then the Spectrum and the BBC.
Played a bit with Fortran and Pascal at school. Wrote my first database on the BBC - no SQL required. :p

I haven't used 2013 or 365 yet - our office is still on 2003 with a select few on 2010.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top