Hi Peter,
Warning: Make a backup copy of your workbook before installing and running
the macro below in case it does not work as you anticipated.
I assume that when you refer to a macro box you mean a button to run a
macro. I also assume from the macro name that this is a Forms Button.
If my assumptions are correct then the way to achieve what you want is to
rename the button caption each time the macro is run. You can then use the
button caption in conjunction with an 'If' statement to determine whether to
apply color or remove color.
The following should achieve what you want. However, you need to identify
the button code name and edit the name in the macro if required. To do this:-
Turn on macro recording.
Right click the button to select it.
Click back on any other cell to deselect the button.
Turn off macro recording.
Find the module where the recording took place and you will see the button
code name in a line of code that looks like this:-
ActiveSheet.Shapes("Button 1").Select
"Button 1" is the code name. If yours is Button 1 then the macro will work
as is but if not then replace Button 1 with your button name everywhere it
appears in the code.
You can remove the recorded macro and module.
You will also need to edit the existing caption on the button and in the
macro so that they match for the initial running of the code.
As a note of interest about the code, the only way I can return or change
the button caption in the code is to use code to select the button first.
Feel free to get back to me if you have any problems.
Sub auditt()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim strCaption As String
Dim cellChanged As Boolean
Set sh1 = Sheets("Original")
Set sh2 = Sheets("Final")
'Select and assign button caption to variable
ActiveSheet.Shapes("Button 1").Select
'Assign button caption to a variable
strCaption = Selection.Characters.Text
'Deactivate the button
Range("A1").Select
If strCaption = "Color the changed cells" Then
cellChanged = False
For Each r In sh1.UsedRange
v1 = r.Value
rr = r.Row
cc = r.Column
v2 = sh2.Cells(rr, cc).Value
If v1 <> v2 Then
sh2.Cells(rr, cc).Interior.ColorIndex = 36
cellChanged = True
End If
Next
If cellChanged = True Then
ActiveSheet.Shapes("Button 1").Select
Selection.Characters.Text = "Remove color from cells"
Range("A1").Select
End If
Else
sh2.UsedRange.Interior.ColorIndex = xlColorIndexNone
ActiveSheet.Shapes("Button 1").Select
Selection.Characters.Text = "Color the changed cells"
Range("A1").Select
End If
End Sub
Regards,
OssieMac
