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

[JackStraw42]

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.

 

[Crystalyzer]

something like this?

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

End Sub

 

[Fluff]

How are you currently doing the conditional formatting for the strikethrough?

 

[andrewmrichards]

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:

..which results in this:

Excel sample calculations.xlsm
	A
1	Test for italic
2	This is not
3	This is
4	This is not
5	This is
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A5	Expression	=isitalics(A2)	text	NO

 

[Fluff]

You could also use the old Get.cell
Create a defined name ( I called it IsItalic) & put this formula in the refers to box

=GET.CELL(21,INDIRECT("rc",FALSE))

Then in conditional formatting you can use

=IsItalic

 

[JackStraw42]

You could also use the old Get.cell
Create a defined name ( I called it IsItalic) & put this formula in the refers to box

=GET.CELL(21,INDIRECT("rc",FALSE))

Then in conditional formatting you can use

=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]

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]

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
1	Test for italic
2	This is not
3	This is
4	This is not
5	This is
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A5	Expression	=isitalics(A2)	text	NO

This worked. Thanks!!

 

[Fluff]

When i scroll up or down, all the text gets changed to a blocky font and looks bold.

It does doesn't it, I've never encountered that before.

 

[andrewmrichards]

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