How to vary the format within cells (i.e. italicise some words and not others) in excel

Question

Could anyone help with adding a macro in excel for varying the formatting within each cell in a column (variable number of entries up to about 5000).

I need to have plant names correctly formatted:

Italise: genus, species, subspecies, or variety

But not italise the words: subsp., var., x, f. aff. or any words within quotation marks ''

i.e. Olea europaea subsp. cuspidata or Malus domestica 'Golden Delicious'

Thanks

score 1 · Answer 1 · answered Oct 24 '16 at 00:40

1

The basic syntax for specifying which characters in a cell you want to italicise is straightforward:

Range("I4").FormulaR1C1 = "Malus domestica 'Golden Delicious'"
Range("I4").Characters(Start:=1, Length:=16).Font.FontStyle = "Regular"
Range("I4").Characters(Start:=16, Length:=19).Font.FontStyle = "Italic"

Just a matter of modifying to suit your application, i.e. figuring out which characters in the string need to be italicised. Best of luck.

answered Oct 24 '16 at 00:40

CustodianOfCode

674
3
12

Thanks, but every cell is different so what I need is one code which italicises all cell content except a defined list of words – RIK Oct 24 '16 at 07:30
That's where you come in :) Your mission is to write code that loops over all applicable cells, and implements the logic you desire to determine where to start and end the italic sections. – CustodianOfCode Oct 24 '16 at 23:44

score 0 · Answer 2 · edited May 23 '17 at 12:00

Since you want to format only some of the characters inside the cell, you could use a regular expression (regexp) instead of conditional formatting.

Read this for an excellent how-to by user Portland Runner.

Also, look at user the answer from brettdj in this, who fumbled out how to only format parts of the cell value.

As for your task:

Sub FormatMyApples()

    ' Format keywords that are NOT wrapped in single quotes
    FormatRegExpItalic _
        "[^'](Olea europaea|cuspidata|Malus domestica|Golden Delicious)[^']" _
        , ActiveSheet.UsedRange
End Sub

And here's sub for the actual formatting

Private Sub FormatRegExpItalic( _
ByVal pattern As String, ByRef range As Variant)

    Dim RegMC As Object
    Dim RegM As Object
    Dim cell As Object

    With CreateObject("vbscript.regexp")
        .Global = True
        .IgnoreCase = True
        .pattern = pattern

        For Each cell In range
            If .test(cell.Value) Then
                Set RegMC = .Execute(cell.Value)
                For Each RegM In RegMC
                    cell.Characters(RegM.FirstIndex + 1, RegM.Length).Font.FontStyle = "Italic"
                Next
            End If
        Next cell
    End With
End Sub

How to vary the format within cells (i.e. italicise some words and not others) in excel

2 Answers2