Conditional formatting – Strike through

Conditional Formatting and Filtering is based on the content of the cell and not the formatting

I would suggest you create a new column just to say whether the cell has strikethrough, then you could filter on this.  It would probably be better practice then to clear formatting on the opriginal column and set a new conditional format to do the strikethrough - that would give you more flexibility for the future.  
This simple macro will insert a column before yours that contain TRUE if the cell has strikethrough, or FALSE if not - just select your range first, then run it.

Sub IDStrikeThrough()

    Dim c As Excel.Range
    Dim rngSource As Excel.Range
    
    Set rngSource = Selection
    rngSource.EntireColumn.Insert
    
    For Each c In rngSource.Cells
        c.Offset(0, -1).Value = c.Font.Strikethrough
    Next c
    
End Sub

Select allOpen in new window

Hi Andrewssd3
I am trying to avoid using VBA for this if at al possible.
Is there any other way of doing it, perhaps by creating an addition column with some sort of formula in it to mimic the VBA you have specified?

Thanks for your help

You can use old Excel 4 macro function to get strike through info "GET.CELL(23,...)" see here:

http://www.youtube.com/watch?v=b1S4UJsL62w

I don't think so - someone else is welcome to correct me, but I don't think there is a built-in function that can identify strikethrough.  The CELL function can get some information about formatting including colour and numeric formatting, but as far as I know not the Font formatting.

OK - I've been corrected before I even pressed submit!

DerZauberer - do these old Excel 4 functions still work in Excel 2010?

OK - interesting.  It does work in 2007 at least, but you have to do some fiddly stuff with named ranges to get it in - personally I'd rather run a simple macro.  Useful information, though - thanks DerZauberer

Yes it's not very comfortable, but if you really really need it, it does still work in Excel 2010, i'm using that ^^

It's not really that fiddly - only one named range required... :)

No sure, it's doable - it just looks like to get it work on a whole column you'd need to embed an OFFSET function as well - it's just a question of taste really.

I'd just use:
=GET.CELL(23,INDIRECT("RC[1]",0))+NOW()*0
to check the adjacent cell.

What's the NOW() for?  Is that a way of making it volatile?  If so, does the INDIRECT not do that anyway?  I think I'm learning more here than GordonMasson :)

Rorya

I am not sure how i use this
Is there any chance you could post a sample sheet showing how it works

Thanks

Hi Rorya
Thanks for that but now i am totaly lost
Your example does exactly what i am looking for but i cant see how it works??
Is =StruckThrough some sort of function thats working on the sheet contents?

It's a defined name, using the formula I posted earlier. It checks the adjacent cell (to the right) to see if it has strikethrough applied to at least the first character.

sorry... i am still strugling here..... do you enter this as a value/formula in a cell?

Not for points as this rorya's solution, but you need to create a new column to left of yours and enter the formula =StruckThrough in the top cell then drag it down as far as you want.

If you are using Excel 2003 or earlier:
Insert-Name-Define
Enter:
StruckThrough
 in the name box and:
=GET.CELL(23,INDIRECT("RC[1]",0))
in the refers to box, then press OK. Then Insert a column and enter =Struckthrough and copy down.

Ahh
I should have said, i am using 2007
I know how to give a cell a name in 2007 using the area to the left of the fx area but it doesnt offer the option you are talking about....as far as i can see.

Thanks for your help guys.
Perfect :)