Clear formatting in a given range of cells

Hi,

I have a relatively simple macro that does exactly what I want it to, its for a checklist at work we use to track orders until completion, when complete, we "gray" out the cells and clear the formatting in that row (A3-W3), keeping only the dates for reference. There's a ton of conditional formatting to clear, so I use the clear function across the entire range of cells then reformat the dates. Here's the macro then the problem I need a solution to.

Sub finished_1()
If MsgBox("Are you sure?", vbYesNo) = vbNo Then Exit Sub
Worksheets("December").Range("A3:W3").ClearFormats
Range("B3").NumberFormat = "mm/dd/yy"
Range("L3").NumberFormat = "mm/dd/yy"
Range("S3:T3").NumberFormat = "mm/dd/yy"
Range("A3:W3").Interior.ColorIndex = 16
End Sub

So, I inserted a button to click to execute the macro. Here's the problem, there are 200 rows per month and twelve sheets, one for each month, which leads to 2400 buttons and macros, yikes. I need a better way!

My thought, instead of a button, I have a free cell in each row where the button is, is there a way I can type some text like "run" in that cell and have that text execute a macro for that row? The macro would need to identify the row I'm in based on the row I'm typing in, if this is possible I could have 1 macro instead of 2400. I like the button but don't see any way to use it and have the macro know which row the button is referencing.

Anyway, I'm open to suggestions, I want it to run all the same commands just have one macro for any row or at most 12 macros since each month may need it own.

Thanks for any help. BTW, I've already inserted 300 macro's and said, Ugh, there has got to be a better way

Mickey

Hi,

Actually the first response's code did the trick except it could be easy to "clear" the wrong row if the user accidentally has their cursor in the wrong row. It does allow me to insert one button and one macro to "clear" any row and I may just go with this.

I've attached a sample workbook. Row 9 is set up to run the "clear" macro. As you will see, its a color coded checklist type system for tracking the progress of an order. All conditional formatting is based on the dates entered in specific cells and today's date, with one extra format based on entering specific text in one cell, there is a lot of it but it works the way I want. Once an order is complete (invoiced), we just want to "gray" out the row. I used clear formatting because of all the conditional formatting and changed the dates cells back to dates for historical reference purposes since "clear formatting" changes the dates.

Keep in mind, their will be 12 worksheets, one for each month, named for that month. So my thought in my first post was, where you see the "done" buttons, is there a way to delete that button and just use that cell to trigger a "clear" macro by typing something in that cell like "DONE".

royUK I tried your code, couldn't figure out how to execute it. Double clicking didn't do anything. Let me download your attachement, I didn't do that yet

Carim I like your approach with the message box, changing the text better than entering the row #, fewer steps for user the better. I'm going to test it

Hi All,

I went with Carim suggestions. I did have to edit it though as I got an error first time I tried, just had to make the last "date" formatting line into 2 separate lines. Here's the final code I am using:

Sub JAN_DONE()
' Generic Version within the Active Worksheet and using Active Cell
' User needs to place his cursor anywhere on the Row to be dealt with
' for example to deal with Row 7 : go to cell A7
If MsgBox("CURSOR IN CORRECT CELL? & ARE YOU SURE?", vbYesNo) = vbNo Then Exit Sub
Dim x As Long
x = ActiveCell.Row
 With Range("A" & x & ":W" & x)
 .ClearFormats
 .Interior.ColorIndex = 16
 End With
 Range("B" & x).NumberFormat = "mm/dd/yy"
 Range("L" & x).NumberFormat = "mm/dd/yy"
 Range("S" & x).NumberFormat = "mm/dd/yy"
 Range("T" & x).NumberFormat = "mm/dd/yy"
End Sub

I did 12 macros, one for each month, just felt safer doing it this way, and assigned a button on each page to execute it. I liked adding a warning to the message box, I also added another "alert" in the cell that used to house the :done" buttons, thats 2 warnings for the user, so if they "clear" the wrong row by accident, no one to blame but themselves. Here's a screenshot:

oops sorry for the multiple screenshots

Quote from Carim

To make your life easier ... if need be, you can have 12 Command Buttons ... but all buttons can point to one single macro ...

Had to laugh at that comment. My life went from 2,400 buttons and 2,400 macro's to 12 buttons and 12 macro's, My life ALREADY got MUCH easier! Thanks again for the help!

BTW, I did 12 macros just in case clicking "done" on one sheet, executed the macro on another sheet. It doesn't but I was preparing for contingencies should I need to change the reference from active sheet to the name of a sheet for example. Anyway, after inserting 400 buttons and 300 macros's before I quit and came here, 12 was a breeze