Forum Discussion

magic_trance's avatar
magic_trance
Copper Contributor
Nov 24, 2021

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

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-cell-row-or-column?redirectedfrom=MSDN

 

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

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    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.

    • magic_trance's avatar
      magic_trance
      Copper Contributor
      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?

Resources