[Solved] Eisenhower Matrix add an extra column

[HarvestChurch]

HI
I have set up ‘How to Make an Eisenhower Matrix Template in Excel (With Easy Steps)’ – BUT
I would like to add an additional Data Validation column so I have Important, Urgent AND Status

Within the Status column I would like the following conditional format of On Hold, Started, Not Started, A/W Input, Completed and once the task is completed in the matrix box I would like the task to be 'strikethrough'

Is this possible and if so, how do I do that?
Thank you

 

[Yousuf Shovon]

Hello HarvestChurch,

Welcome to our website! I understand you wish to format the tasks using Conditional Formatting in your Eisenhower Matrix. Though I think using VBA would be the better option. But it is also possible through Conditional Formatting. I assume you already have added the Data Validation column. I am going to show you how to add that just in case.

• Firstly, select K5 and go to Data > Data Tools > Data Validation.

• Consequently, the Data Validation dropdown box pops up.
• There, click on Settings > List > and write the elements you wish to add using Commas ( , ) in between.

• Next, select the cell or range you wish to apply Strikethrough and click Home > Conditional Formatting > New Rule.

• Further, select the Use a formula to determine which cells to format option.
• Type the following formula in the format values where the formula is true box and tap Format:

=$k$5="Completed"

• Subsequently, check the Strikethrough option and tap Ok.
• Repeat this for each cell individually.

• Thus, you obtain Strikethrough whenever you input Completed in the Status column.

Hope this helps. Let us know if you would like to apply an easier version (Excel VBA).

Best Regards,
Yousuf Shovon

 

[HarvestChurch]

Hi Yousuf
Thank you for your prompt response and VERY helpful instructions.
I didn't even think of a VBA, but that makes sense. Are you able to talk me through setting up a VBA in this scenario - anything for 'simple'
Thank you

 

[Yousuf Shovon]

Hello HarvestChurch,

First, I would like to apologize for the late response. I am really delighted to know the previous reply was useful. For the suggested VBA method, it would be really cumbersome to implement for the same scenario. However, I can provide a VBA code that is triggered when you manually double-click on a cell in the matrix when the task is completed. The VBA code is:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = False
    If Target.Column >= 4 And Target.Column <= 6 And Target.Row >= 6 And Target.Row <= 13 Then
        Cancel = True
        If Target.Value <> "" Then
            If Target.Font.Strikethrough = True Then
                Target.Font.Strikethrough = False
            Else
                Target.Font.Strikethrough = True
            End If
        End If
    End If
End Sub
Sub Strikethrough()
End Sub

If there is a value in the cell, the code thoroughly toggles the strikethrough property of the cell's font. If the font is currently not strikethrough, the code applies strikethrough formatting to the font, and if the font is currently strikethrough, the code removes the strikethrough formatting.

Hope this works for you. Also, adjust your Target row & column numbers accordingly.

Best Regards.

 

[HarvestChurch]

Hello HarvestChurch,

First, I would like to apologize for the late response. I am really delighted to know the previous reply was useful. For the suggested VBA method, it would be really cumbersome to implement for the same scenario. However, I can provide a VBA code that is triggered when you manually double-click on a cell in the matrix when the task is completed. The VBA code is:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = False
    If Target.Column >= 4 And Target.Column <= 6 And Target.Row >= 6 And Target.Row <= 13 Then
        Cancel = True
        If Target.Value <> "" Then
            If Target.Font.Strikethrough = True Then
                Target.Font.Strikethrough = False
            Else
                Target.Font.Strikethrough = True
            End If
        End If
    End If
End Sub
Sub Strikethrough()
End Sub

If there is a value in the cell, the code thoroughly toggles the strikethrough property of the cell's font. If the font is currently not strikethrough, the code applies strikethrough formatting to the font, and if the font is currently strikethrough, the code removes the strikethrough formatting.

Hope this works for you. Also, adjust your Target row & column numbers accordingly.

Best Regards.

Thank you Yousuf, Have to say none of this makes sense to me but I'll give it a go