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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?

 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

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