Conditional Formatting by Cell Format

7headed

New Member
Joined
Jan 13, 2014
Messages
33
Hello,

I would like to like to apply conditional formatting to any cell that has "strikethrough" fonts. My spreadsheet is almost 2,000 rows full and it would take an awful long time to do it manually. I didn't notice anywhere in the drop down menu that you could use formatting as one of the parameters. Any help would be appreciated.

Thank you
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Chris Macro

Well-known Member
Joined
Nov 2, 2011
Messages
1,341
I don't believe there is any way to do this with conditional Formatting. The only way that I could think of would be to add a VBA function in that outputs a True or False and then you could filter on the Trues and apply your formatting. I'm not sure how familiar you are with VBA but I will try to explain.

Insert this code into a module in your desired workbook:

<font face=Calibri><SPAN style="color:#00007F">Function</SPAN> IsStrikeThrough(rng <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br><br><SPAN style="color:#00007F">If</SPAN> rng.Font.Strikethrough = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>    IsStrikeThrough = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">Else</SPAN><br>    IsStrikeThrough = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

On your spreadsheet you should be able to type in a formula that corresponds with the function code. Try typing in =IsStrikeThrough() and then you can select the cell that holds the text that you want to determine if it is struck through or not. Just like a normal Excel function
 

7headed

New Member
Joined
Jan 13, 2014
Messages
33
I have the macro inserted. When I type the formula you gave me for a cell that is struck through, it tells me "true". But, how do I get the cell that the formula is referencing to format? I basically want every cell with strikethrough formatting to turn red font. Thanks again.
 

Chris Macro

Well-known Member
Joined
Nov 2, 2011
Messages
1,341
Assuming your data starts in cell A1 and your strikethrough formula is in B1....you could set up a custom conditional formula (highlight all your data in Column A and add the conditional formula). That formula would be =$B1 = True. Then format it however you would like.
 

7headed

New Member
Joined
Jan 13, 2014
Messages
33

ADVERTISEMENT

Yes!!! Thanks Chris.
 

7headed

New Member
Joined
Jan 13, 2014
Messages
33
Oops, I spoke too soon. It looks like I have to update the cell with the formula in it every time a strikeout a new cell. If I strikeout a cell, nothing happens. If I go over to the formula, and either copy from the cell above or just hit F2 and enter, then the conditional format kicks in. Any Ideas about this?

 

Chris Macro

Well-known Member
Joined
Nov 2, 2011
Messages
1,341

ADVERTISEMENT

Try this:

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> IsStrikeThrough(rng <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br><br>Application.Volatile<br><br><SPAN style="color:#00007F">If</SPAN> rng.Font.Strikethrough = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>    IsStrikeThrough = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">Else</SPAN><br>    IsStrikeThrough = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

I also noticed that the formulas will recalculate if you use the Format Painter to copy and add a strikethrough instead of going through the the Cell Format dialog box. That might be a workaround.
 

7headed

New Member
Joined
Jan 13, 2014
Messages
33
I updated the macro and it still does not update the formula when I do a manual strikeout. However, the painter does work and that's good enough for me. Thank you my friend.
 

Chris Macro

Well-known Member
Joined
Nov 2, 2011
Messages
1,341
No problem...I think doing a manual strikeout doesn't cause the spreadsheet to recalculate its formulas since its just changing a cell property. But glad you were able to get it working!
 

7headed

New Member
Joined
Jan 13, 2014
Messages
33
I love Excel; but it can be downright frustrating sometimes! Next area for me to learn is VBA.
Thank you very much again for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,621
Members
414,081
Latest member
Subaru_Steve

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