If font is strikethrough...

[dl7631]

Hi!
I have a string entry in A1 that is in strikethrough font. In B1, I'd like to write a formula - something like:
If (effect in A1 = strikethrough, 1, 0)
Is it possible? What is the correct formula?
Thanks a lot!

 

[Lokkenreload]

Hi Guys

I am looking for something similar to the VBA code posted here:


Function ist(r1 As Range) As Byte Application.Volatile ist = abs(r1.Font.Strikethrough) End Function

Could you please edit it for me so that the cell reference is not in column A but rather can be referenced to any cell/column

e.g. if I type the formala into Y1 =IST(W1) then it will use the function correctly.

Thank a lot

G
</pre>

 

[Lokkenreload]

SOLVED

Thanks

 

[RoryA]

I'm curious about your question - the function already worked as you wanted.

 

[Lokkenreload]

Strange... I couldn't get it to work properly.

When I had values that were "strikethrough" in Col W it wouldn't activate the formula in Col Y. I tried it so that the strikethrough was in Col A and it worked fine so I thought it was a referencing issue.

I ended up not using VBA, rather used an if statement containing the conditional formatting formula to identify the amounts to be "struck-through".

I have little to no experience using VBA so it could also be my mistake that it didn't work as intented.

I'm just very relieved that my worksheet is finally working nicely : )

G

 

[RoryA]

Changing the font style would not cause the function to recalculate - which is a good reason not to try and use cell formatting as data - but it would recalculate whenever the worksheet did.

 

[Scott Huish]

In addition to what RoryA said, if you use a function that relies on formatting, because it doesn't cause recalculation, you need to hit F9 to force a recalculation.

 

[IceSryn]

Create a named range, call it Strike

in the 'refrs to' box enter =GET.CELL(23,INDIRECT("RC[-1]",0))

Then use the formula

=IF(Strike,1,0)

Note that this assumes the cell with the strikethru text will be to the immediate left of the formula.

Thank you for this. I love it. This works on MS Excel 2007 in the year 2019. Also, no F9 refresh needed.

Again, thank you.


IcsSryn

 

[Jaafar Tribak]

works thru cell strike/un-strike without F9 refresh needed

Are you sure it works without a recalculation ?!!!!

 

[IceSryn]

Are you sure it works without a recalculation ?!!!!

<strike>Yes, on MS Excel 2007. I do have the Excel Options > Formulas > Calculation options > Workbook Calculation set to Automatic</strike>

Oh yeah, it didn't. I used Format Painter and saw the result cell showed TRUE. When I used basic set cell to Strikethrough, the result cell remained FALSE.

My bad. Apologies.


IceSryn