Formatting specific text within a cell.

chipgiii

Board Regular
Joined
Nov 24, 2008
Messages
137
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:

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,295
Office Version
  1. 365
Platform
  1. Windows
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
 

chipgiii

Board Regular
Joined
Nov 24, 2008
Messages
137
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.
 

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,295
Office Version
  1. 365
Platform
  1. Windows
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
 

chipgiii

Board Regular
Joined
Nov 24, 2008
Messages
137

ADVERTISEMENT

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.
 

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,295
Office Version
  1. 365
Platform
  1. Windows
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
 

chipgiii

Board Regular
Joined
Nov 24, 2008
Messages
137

ADVERTISEMENT

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.
 

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,295
Office Version
  1. 365
Platform
  1. Windows
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.
 

chipgiii

Board Regular
Joined
Nov 24, 2008
Messages
137
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.
 

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,295
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,424
Messages
5,528,682
Members
409,830
Latest member
KT50

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top