Conditional formatting applied to word or phrase within text

Casey1981

New Member
Joined
Dec 21, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello. Please forgive what might be a goofy question. I am working with a workbook that includes text previously manipulated with HTML. Words & phrases intended to be italicized appear as "<i> text </i>". Does anyone know an easy solution to this? Am I able to italicize these words & phrases (rather than entire cell contents) with a conditional formatting rule? If so, will I be able to retain the italics after deleting the remaining formatting marks with the find & replace function? Thank you for any help you can offer.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Welcome to the MrExcel board!

Am I able to italicize these words & phrases (rather than entire cell contents) with a conditional formatting rule?
No, that is not possible.

A solution may be possible using a macro. Would that be acceptable?

If so, can you give us a small set of dummy data (with XL2BB) that shows any sort of variety of formatting required (or is it only italics?) that we can test with and explain the requirement in relation to that sample data?
 
Upvote 0
I'm about to go off-forum so lets try a small example to see if this is any use.

Sample data

Casey1981.xlsm
A
1Not italics <i> italics </i> not italics <i> italics </i>
2You <i> must </i> be <i> kidding about </i> that!
Sheet1


VBA Code:
Sub FixItalics()
  Dim RX As Object, M As Object
  Dim cell As Range
  Dim j As Long
  
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "(<i>)(.*?)(</i>)"
  Application.ScreenUpdating = False
  For Each cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
    Set M = RX.Execute(cell.Value)
    cell.Value = RX.Replace(cell.Value, "$2")
    For j = 0 To M.Count - 1
      cell.Characters(M(j).firstindex - 7 * j + 1, M(j).Length - 7).Font.Italic = True
    Next j
  Next cell
  Application.ScreenUpdating = True
End Sub

Sheet after the code has been run

1608552739329.png
 
Upvote 0
Solution
I'm about to go off-forum so lets try a small example to see if this is any use.

Sample data

Casey1981.xlsm
A
1Not italics <i> italics </i> not italics <i> italics </i>
2You <i> must </i> be <i> kidding about </i> that!
Sheet1


VBA Code:
Sub FixItalics()
  Dim RX As Object, M As Object
  Dim cell As Range
  Dim j As Long
 
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "(<i>)(.*?)(</i>)"
  Application.ScreenUpdating = False
  For Each cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
    Set M = RX.Execute(cell.Value)
    cell.Value = RX.Replace(cell.Value, "$2")
    For j = 0 To M.Count - 1
      cell.Characters(M(j).firstindex - 7 * j + 1, M(j).Length - 7).Font.Italic = True
    Next j
  Next cell
  Application.ScreenUpdating = True
End Sub

Sheet after the code has been run

View attachment 28340
THANK YOU, PETER!!! This worked perfectly! You really saved the day. :) :) :)
 
Upvote 0
You are very welcome. Glad it worked for you. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

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