Macro to reformat

[TWienholz]

Hey guys! Can someone please help me with a macro I'm having issues with. I've created a macro that will reformat a cell in a protected sheet to it's original formatting, however it's only working in the cells that I initially recorded the macro in. I need it to work anywhere on the sheet when necessary. I have been experimenting with the range values to try and get this to work, but it's been to no avail. Can someone please help. Here is the current VBA code:
Sub ReformatCell()
'
' ReformatCell Macro
' This macro reformats a cell to its original formatting
'
' Keyboard Shortcut: Ctrl+y
'
Range("C20:d20").Select
Sheets("DTP Audit Checklist").Select
ActiveSheet.Unprotect
Range("C22:G22").Select
Selection.Copy
Range("C20:d20").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.ClearContents
Sheets("DTP Audit Checklist").Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False
End Sub

The cells I am trying to target are c20:d20 through c84:d84 and then ag20:ai20 through ag91:ai91. Please let me know if I need to clarify anything, but I would appreciate any help you can offer. Thank you so much!

 

[TWienholz]

Try this.

Sub ReformatCell()
    '
    ' ReformatCell Macro
    ' This macro reformats a cell to its original formatting
    '
    ' Keyboard Shortcut: Ctrl+y
    '
   
    Dim WS As Worksheet
    Dim rngSourceFormat As Range
    Dim rngDestFormat As Range
    Dim rngCheckBoxes As Range
    Dim ColStr As String
   
    On Error Resume Next
    Set WS = Sheets("DTP Audit Checklist")
    On Error GoTo 0
   
    If WS Is Nothing Then
        MsgBox "Error - Cannot locate required worksheet '" & ActiveWorkbook.Name & "'", vbOKOnly Or vbCritical, ActiveWorkbook.Name
        Exit Sub
    End If
   
    WS.Activate

    Set rngCheckBoxes = Application.Union(WS.Range("C20:D84"), WS.Range("AG20:AG91"), WS.Range("AI20:AI91"))
   
    If Application.Intersect(ActiveCell, rngCheckBoxes) Is Nothing Then
        MsgBox "The cell you have selected for reformatting (" & ActiveCell.Address & ") is not a DTP or QC cell. Please try again. ", vbOKOnly Or vbCritical, Application.Name
        Exit Sub
    End If
   
    WS.Unprotect
    ColStr = Split(ActiveCell.Address, "$")(1)
   
    Select Case ColStr
        Case "C"
            Set rngDestFormat = ActiveCell.Resize(1, 2)
        Case "D"
            Set rngDestFormat = ActiveCell.Offset(0, -1).Resize(1, 2)
        Case "AG"
            Set rngDestFormat = ActiveCell.Resize(1, 3)
        Case "AI"
            Set rngDestFormat = ActiveCell.Offset(0, -2).Resize(1, 3)
    End Select
   
    Select Case rngDestFormat.Row
        Case Is = rngCheckBoxes.Cells(1, 1).Row
            Set rngSourceFormat = rngDestFormat.Offset(1)
        Case Else
            Set rngSourceFormat = rngDestFormat.Offset(-1)
    End Select
   
    rngSourceFormat.Copy
    rngDestFormat.PasteSpecial Paste:=xlPasteFormats
    rngDestFormat.ClearContents    'optional
   
    Application.CutCopyMode = False
    WS.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False
End Sub

Hey RLV01. Sorry for the delay. The last few days have been crazy busy. I updated the code you gave me to what you posted above, but nothing happens. When I run the macro, the screen basically blips quickly but nothing changes. I'm not getting any error msgs, but nothing is reformatting either.

 

[TWienholz]

Hey RLV01. Sorry for the delay. The last few days have been crazy busy. I updated the code you gave me to what you posted above, but nothing happens. When I run the macro, the screen basically blips quickly but nothing changes. I'm not getting any error msgs, but nothing is reformatting either.

I take that back. I didn't notice it before, but what it seems to be doing is just creating a new template instead of reformatting the affected cells in the current template. Does that make sense? So I went from DTP Audit Checklist Template to DTP Audit Checklist Template1.

 

[rlv01]

I take that back. I didn't notice it before, but what it seems to be doing is just creating a new template instead of reformatting the affected cells in the current template. Does that make sense? So I went from DTP Audit Checklist Template to DTP Audit Checklist Template1.

Nothing in the code I posted will do that. I'm assuming you are somehow running another macro, or else you have made some modifications. I recommend that you set a breakpoint at the following line of code, and then single-step the execution using the 'F8' key to diagnose what is going on.

Additional info:
How to post your code using code tags:

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

Here is an introduction to VBA debugging:
VBA: How to Debug Code