Macro to undo Macro?

[Zach]

Ok I'm creating a date log for my company so that when they complete a task they can just click the complete button and it will change the color and font of the cell. However I need to create a marco to reverse the effects of the complete macro.

So the attached file shows some rows start highlighted Yellow and some that aren't, and if I clicked on Cell F51 & F52 and clicked complete it would turn it Bold, blue cell and white lettering. Now I need to create a marco that allows me to basically undo the complete marco and set the cells back to their original state (F51 being yellow and F52 with no Fill).

This whole worksheet when complete will be locked and protected so that the only way to format any cells will be through the two macros.

I haven't done enough macro work to know if this is possible. Can someone please advise?

Thanks

Zach

 

[NARAYANK991]

Hi Zach ,

More detailing is required ; when you run the Undo_Complete macro , how can the macro find out what was the original colour of cell F51 ?

Secondly , will you select multiple cells within a single column before you run the Undo_Complete macro , or can you select a multiple-row , multiple-column range ?

Narayan

 

[Luke M]

I'd suggest setting up some extra columns to act as a master/key for the correct formatting you want the row to have. Then the "undo" macro will actually just copy the formatting for the cell in question from that cells corresponding master cell.

Otherwise, w'd have to setup several variable to keep track of different cell states, and it still wouldn't be enough if you need to be able to undo an action after the workbook has been closed/reopened.

 

[Zach]

Hi Zach ,

More detailing is required ; when you run the Undo_Complete macro , how can the macro find out what was the original colour of cell F51 ?

Secondly , will you select multiple cells within a single column before you run the Undo_Complete macro , or can you select a multiple-row , multiple-column range ?

Narayan

That's the issue I'm having is I don't know how to configure to make the marco know what the original color was. As to part 2, based on my knowledge of how this spreadsheet will be used multiple cells in a single column might be selected to run the undo_complete macro but never multiple rows or multiple columns.

 

[Zach]

I'd suggest setting up some extra columns to act as a master/key for the correct formatting you want the row to have. Then the "undo" macro will actually just copy the formatting for the cell in question from that cells corresponding master cell.

Otherwise, w'd have to setup several variable to keep track of different cell states, and it still wouldn't be enough if you need to be able to undo an action after the workbook has been closed/reopened.

Luke,
Is there somewhere within this website that can put me on the right path on how to create a "master/key" for the formatting? The breakdown of the color scheme is pretty set, the really important dates will be filled yellow and everything else will still remain unfilled.

 

[Luke M]

Hi Zach.
What I meant was, just setup a column in your workbook to act as the template. So, let's say you format column Z as the template showing correct initial formatting for each row. Then the "undo" macro is something like:

Sub UndoMacro()
Dim c As Range
Dim cRow As Long
 
Application.ScreenUpdating = False
For Each c In Selection
    'Which row do we need to look at?
    cRow = c.Row
    
    'Copy from the master cell
    Cells(cRow, "Z").Copy
    
    'Paste the formatting
    c.PasteSpecial xlPasteFormats
    Application.CutCopyMode = False
Next c
Application.ScreenUpdating = True
End Sub