Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1624
  • Last Modified:

Conditional formatting – Strike through

I have a column of data in Excel  and some of the entries have a strike through.
Is it possible to set a conditional formatting rule to highlight the cells that have the strike through?
It would also be usefully if I could filter on cells that have a strike through using the normal filter… can this be done?

Thanks
0
GordonMasson
Asked:
GordonMasson
  • 8
  • 6
  • 5
  • +2
2 Solutions
 
pritamduttCommented:
Conditional Formatting and Filtering is based on the content of the cell and not the formatting
0
 
andrewssd3Commented:
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

Open in new window

0
 
GordonMassonAuthor Commented:
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
DerZaubererCommented:
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
0
 
andrewssd3Commented:
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.
0
 
andrewssd3Commented:
OK - I've been corrected before I even pressed submit!

DerZauberer - do these old Excel 4 functions still work in Excel 2010?
0
 
andrewssd3Commented:
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
0
 
DerZaubererCommented:
Yes it's not very comfortable, but if you really really need it, it does still work in Excel 2010, i'm using that ^^
0
 
Rory ArchibaldCommented:
It's not really that fiddly - only one named range required... :)
0
 
andrewssd3Commented:
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.
0
 
Rory ArchibaldCommented:
I'd just use:
=GET.CELL(23,INDIRECT("RC[1]",0))+NOW()*0
to check the adjacent cell.
0
 
andrewssd3Commented:
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 :)
0
 
GordonMassonAuthor Commented:
Rorya

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

Thanks
0
 
Rory ArchibaldCommented:
@Stuart
Yes, the NOW()*0 was to make it volatile - I originally just had a relative reference in there, but then added the INDIRECT so it would work on any sheet and forgot to remove the NOW().

@Gordon,
See attached. Note that applying strikethrough to a cell's font would not automatically cause a recalc, but the formulas will update with an f9.
Strikethrough.xls
0
 
GordonMassonAuthor Commented:
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?
0
 
Rory ArchibaldCommented:
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.
0
 
GordonMassonAuthor Commented:
sorry... i am still strugling here..... do you enter this as a value/formula in a cell?
0
 
andrewssd3Commented:
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.
0
 
Rory ArchibaldCommented:
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.
0
 
GordonMassonAuthor Commented:
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.
0
 
andrewssd3Commented:
Use the Name Manager button on the Formulas tab
0
 
GordonMassonAuthor Commented:
Thanks for your help guys.
Perfect :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 8
  • 6
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now