Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Jul 2014
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formatting to format only part of a cell with strikethrough?

    Hello,

    I have come unstuck trying to figure out how to achieve the result I need.

    I'm a marriage celebrant, and we've recently been informed that certain forms have been changed. I keep a database of my clients (Excel spreadsheet) which I've set up to automatically generate the required forms with the necessary details, in individual worksheets. (It all looks hideously complicated, but it works.)

    However, with the new Notice of Intended Marriage, there is no way that I can see to have the text automatically struck through as appropriate. The previous form didn't distinguish between Australian and foreign passports, whereas this one does.

    Australian† or foreign† passport produced <-- that's the line of text in question.

    It needs to be as above as the default text, but with the text "Australian†" given the strikethrough treatment when either the bride or groom supply an Australian passport as ID; with the text "foreign†" struck through when either the bride or groom supply a foreign passport as ID; with "Australian† or foreign†" given the strikethrough when one of the couple provides an Australian passport and the other provides a foreign passport; and the default text (no strikethrough) when both the bride and the groom provide another means of ID (no passport offered as ID).

    Is there a way to get the conditional formatting to strikethrough PART of the text as needed?

    Or is there another way to achieve the result?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Conditional formatting cannot format PART of the text. It is all or none. Excel requires explicit formatting to do partial.

    Options could include using a macro to change it, creating 3 lines (one for each option) and then hiding/unhiding the row(s) (again via macro) or hiding the appropriate text (but keeping the row) with cond formatting. It would also be possible to separate the single line into partial phrases in separate cells and then use cond formatting to strikethrough the particular cells.

    Steve

  3. #3
    New Lounger
    Join Date
    Jul 2014
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks; at least I know that what I wanted can't work the way I'd hoped.

    Ah well... I've achieved a bearable result by inserting extra columns, narrowing other columns and checking repeatedly in Print Preview.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Does it have to be a true strikethrough? Could you display something like:

    Australian† or foreign† passport produced
    vs
    xxxxxxxxxxxxxforeign† passport produced
    vs
    Australian† xxxxxxxxxxx passport produced

    ore even a cleaner version with no striking out:
    Australian† or foreign† passport produced
    vs
    Foreign† passport produced
    vs
    Australian† passport produced


    Those could be done with a IF formula based on examining some cells...

    Steve

  5. #5
    New Lounger
    Join Date
    Jul 2014
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, Steve,

    Unfortunately I have no leeway in this... It's an official document that has got to be generated according to the prescribed form with no changes or editing - or I can use the official PDFs or Word documents and just fill them out by hand or by form entry for each client. (But I don't want to do that, of course; that's why I have a spreadsheet-based solution so that the client information is entered once, and then it automatically populates each required form and record and so on.

    I shouldn't complain! The Conditional Formatting such as it is has been very useful.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    If the cond formatting with cells is too difficult, a macro could be created which explicitly does the striketrhough in the cell or you explicitly do it manually...

    Steve

  7. #7
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,350
    Thanks
    48
    Thanked 273 Times in 251 Posts
    almirena,

    I am going to take a wild stab at what I think you are trying to do. Here is some code that will do the following:

    If you click on a cell with the text "Australian† or foreign† passport produced" a text box will pop up and prompt you if you want to strikethough "Australian†", "foreign†", or neither. Depending on your selection, that part of the text will be struckthrough.

    Now this can be also modified to conditionally make the strikethrough based on the value of another cell

    strikethrough.png

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    '-----------------------------------------
    'VALIDATE IF ROUTINE SHOULD RUN
    If Target.Count > 1 Then Exit Sub  'IF MORE THAN 1 CELL SELECTED
    If Target = "Australian† or foreign† passport produced" Then
        Target.Select
    '-----------------------------------------
    'SETUP MESSAGE BOX
        Strikethrough = MsgBox("Do you want to Australian† strikethrough?" & Chr(13) & _
            "Click Yes- Australian† strikeThrough" & Chr(13) & _
            "Click No- foreign† strikethrough" & Chr(13) & _
            "Click Cancel- No trikethrough", vbYesNoCancel + vbQuestion)
    '-----------------------------------------
    'STRIKETHROUGH DESIRED TEXT
    Select Case Strikethrough
        Case 6
            Target.Characters(Start:=1, Length:=11).Font.Strikethrough = True
            Target.Characters(Start:=16, Length:=8).Font.Strikethrough = False
        Case 7
            Target.Characters(Start:=1, Length:=11).Font.Strikethrough = False
            Target.Characters(Start:=16, Length:=8).Font.Strikethrough = True
        Case vbCancel
    End Select
    '-----------------------------------------
    Target.Offset(1, 0).Select
    End If
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2014-07-27 at 18:32. Reason: spelling

  8. #8
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,350
    Thanks
    48
    Thanked 273 Times in 251 Posts
    While my first post lets you choose which part to strikethrough, these codes will do it automatically based on the values of other cells.

    Here is the code if "Australian†" or "foreign†" is entered in any cell on the sheet then all cells with the text "Australian† or foreign† passport produced" will have the non-matching part struckthrough.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    '----------------------------------------
    'DECLARE AND SET VARIABLES
    Dim Cell As Range
    Dim Rng As Range
    Set Rng = ActiveSheet.UsedRange
    '----------------------------------------
    'STRIKETHROUGH BASED ON VALUE ENTERED IN ANOTHER CELL
    If Target = "Australian†" Or Target = "foreign†" Then
        For Each Cell In Rng
            If Cell = "Australian† or foreign† passport produced" Then
                If Target = "foreign†"  Then
                    Cell.Characters(Start:=1, Length:=11).Font.Strikethrough = True
                    Cell.Characters(Start:=16, Length:=8).Font.Strikethrough = False
                ElseIf Target = "Australian†" Then
                    Cell.Characters(Start:=1, Length:=11).Font.Strikethrough = False
                    Cell.Characters(Start:=16, Length:=8).Font.Strikethrough = True
                End If
            End If
        Next Cell
    End If
    End Sub
    Use the following code if you know what cells contain the text "Australian† or foreign† passport produced" (label cell) and the entry choice. Change A20 and B20 to perspective cells.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$20" Then  'ENTRY CHOICE CELL
    '----------------------------------------
    'DECLARE AND SET VARIABLES
        Dim Cell As Range
        Set Cell = Range("A20") 'LABEL CELL
    '----------------------------------------
    'STRIKETHROUGH CELL A20 BASED ON VALUE ENTERED IN CELL B20
        If Target = "foreign†" Then
            Cell.Characters(Start:=1, Length:=11).Font.Strikethrough = True
            Cell.Characters(Start:=16, Length:=8).Font.Strikethrough = False
        ElseIf Target = "Australian†" Then
            Cell.Characters(Start:=1, Length:=11).Font.Strikethrough = False
            Cell.Characters(Start:=16, Length:=8).Font.Strikethrough = True
        End If
    End If
    End Sub
    Last edited by Maudibe; 2014-07-27 at 19:37.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,108
    Thanks
    13
    Thanked 38 Times in 37 Posts
    These are really SLICK.

  10. #10
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,350
    Thanks
    48
    Thanked 273 Times in 251 Posts

  11. #11
    New Lounger
    Join Date
    Jul 2014
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Slick is indeed the word!

    At the moment I have two very simple pieces of conditional formatting for both bits of the text that are either struckthrough or not, depending upon whether the clients presented either an Australian or a foreign passport (or both - if the prospective groom presents an Australian passport and the bride presents a foreign passport, for instance). If the condition is met, then the cell's text has the strikethrough format applied. If not, then not. Two cells - one containing Australian†, one containing foreign†.

    Of course, to get this to work, I had to insert code (using CONCATENATE and INDIRECT) into the reference cell which copied across values from another worksheet in the document. That is decidedly less elegant a solution than you've provided...

  12. #12
    New Lounger
    Join Date
    Jul 2014
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ah - hmm... I will have a play with this, as one of the options needs to be both Australian† and foreign† being formatted with strikethrough.


    It's a little ironic is that the PDF of this Notice which can be downloaded from the Attorney-General's department (as a fillable form) doesn't strike through the text that is supposed to be struck through, once the client's information is entered. The Celebrant is still required to do that manually, it seems.

    MUCH easier (and less chance of overlooking any necessary steps) when the form is generated from my spreadsheet.
    Last edited by almirena; 2014-08-05 at 19:43.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •