Ctrl + Z stopped working after using a VBA macro to highlight the row/column after clicking on it

New Contributor

I'm using the second following VBA formula to highlight the row/column after clicking on it that I found here (: https://docs.microsoft.com/pt-br/office/vba/excel/concepts/cells-and-ranges/highlight-the-active-cel...

 

If Target.Cells.Count > 1 Then Exit Sub

    Application.ScreenUpdating = False

    ' Clear the color of all the cells

    Cells.Interior.ColorIndex = 0

    With Target

        ' Highlight the entire row and column that contain the active cell

        .EntireRow.Interior.ColorIndex = 8

        .EntireColumn.Interior.ColorIndex = 8

    End With

    Application.ScreenUpdating = True

 

1. After enabling the macro and saving the spreadsheet as a macro the Ctrl + Z function stopped working - so I can't revert what I type or do in any cell (Yes, I can select the cell and press delete - but Ctrl + Z is simply more practical.).

 

2. How can I edit the macro to make the row be highlighted only from A column until the I column?

 

Thanks in advance.

3 Replies

@magic_trance As soon as you run VBA code that changes anything in the worksheet, the undo stack (Ctrl-Z) is cleared. Google for that and you'll find many references describing this fact. Some also describe tricks and workarounds that will enable you to roll back previous actions, but as I understand, that involves more VBA coding.

That's unfortunate, but I'll look into these tricks you mentioned. Thanks for taking your time helping me.

Any idea in how to fix the macro though?

@magic_trance Not using VBA much myself so can't help you fixing it, sorry.