Conditional Formatting: Highlight cells that are italic? (or other workaround)

JackStraw42

New Member
Joined
Aug 19, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
Hi guys. I'm trying to figure out a way to make certain cells highlight based on a condition that I can trigger with a shortcut. I'm already doing this with the strikethrough shortcut + conditional formatting. I can highlight a group of cells, hit ctrl-5, and all the text gets a strikethrough and cells are highlighted grey. I would like to create a second condition along those same lines, but that doesn't involve a strikethrough. Italic (ctrl-i) would be an ideal condition, but I can't figure out a way to do it. I've tried "=isitalic(A1) = TRUE" but that didn't work.

Is there a way that I can make all cells containing italicized text automatically highlight yellow? That is, if I hit ctrl-i, that cell goes to italic and is also highlighted yellow, and will remain that way between sessions? If not, can you think of an alternative shortcut that I could use to do it? It needs to be the sort of rule that overrides user actions. That is, if the condition is formatting the cell yellow, and the user manually chooses a green highlight for that cell, the yellow persists. This is how it works with my strikethrough condition.

Thanks for any advice you can offer.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
something like this?

VBA Code:
Sub MakeItalicYellow()
    Dim r As Range
    
    Set r = Selection
        
    r.Font.Italic = True
    r.Interior.Color = 65535

End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,092
Office Version
  1. 365
Platform
  1. Windows
How are you currently doing the conditional formatting for the strikethrough?
 

andrewmrichards

New Member
Joined
Aug 28, 2013
Messages
20
Hi
You could do this with a user defined function to test if the cell is italicised, which you could then call with a conditional format.
The function would look like this:

Public Function IsItalics(rng As Range) As Boolean
IsItalics = rng.Font.Italic
End Function

And the conditional format dialog box would then be set up like this:
Conditional format IsItalics.png

..which results in this:

Excel sample calculations.xlsm
A
1Test for italic
2This is not
3This is
4This is not
5This is
Sheet3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A5Expression=isitalics(A2)textNO
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,092
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You could also use the old Get.cell
Create a defined name ( I called it IsItalic) & put this formula in the refers to box
Excel Formula:
=GET.CELL(21,INDIRECT("rc",FALSE))
Then in conditional formatting you can use
Excel Formula:
=IsItalic
 

JackStraw42

New Member
Joined
Aug 19, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
You could also use the old Get.cell
Create a defined name ( I called it IsItalic) & put this formula in the refers to box
Excel Formula:
=GET.CELL(21,INDIRECT("rc",FALSE))
Then in conditional formatting you can use
Excel Formula:
=IsItalic

Thanks everyone! This is the solution I went with. It worked within my current knowledge set, as I'm unfamiliar with using VBA code at this stage. It's also the same solution i used for strikethrough, exceptI didn't have to define that name. It maybe be a KuTools thing, but I don't remember.

I appreciate everyone taking the time to help me! This is going to be a huge time saver!
 

JackStraw42

New Member
Joined
Aug 19, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Ok, scratch that. Using the "Get.Cell" solution creates a display problem. When i scroll up or down, all the text gets changed to a blocky font and looks bold. The itallic is gone. If I close and re-open the document, it looks normal... until i scroll, at which point everything goes back to the blocky bold text. The actual fonts in the cells are the same, it's just the way they display. If I remove the defined name, the problem goes away. Very odd!
 

JackStraw42

New Member
Joined
Aug 19, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
Hi
You could do this with a user defined function to test if the cell is italicised, which you could then call with a conditional format.
The function would look like this:

Public Function IsItalics(rng As Range) As Boolean
IsItalics = rng.Font.Italic
End Function

And the conditional format dialog box would then be set up like this:
View attachment 45218
..which results in this:

Excel sample calculations.xlsm
A
1Test for italic
2This is not
3This is
4This is not
5This is
Sheet3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A5Expression=isitalics(A2)textNO
This worked. Thanks!!
 

andrewmrichards

New Member
Joined
Aug 28, 2013
Messages
20
This worked. Thanks!!
Glad to have helped!

Just a quick point, as you mentioned that you're relatively new to VBA - you need to ensure that your function is in a Standard Module. In other words, it should be in a module called Module1 (or similar) which will show under "Modules" in the project explorer, not in one of the class modules such as Sheet1, otherwise you run the risk that the function will only work on that one sheet, not all of them.

Best regards
Andrew
 

Forum statistics

Threads
1,144,575
Messages
5,725,088
Members
422,590
Latest member
Mikeyyy

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
Top