Need the Facility Ratings & SOLs (Lines) to unfilter, unhighlight and change font to black. Need Line Update to clear out all cells shown.

[Nlhicks]

This currently clears the Line Update sheet but it also removes all of the color from the line update page which is suppose to happen on the Facility Ratings & SOLs (Lines) page. Any suggestions to help with this??

Sub ClearAll1()
    Dim blnEnd            As Boolean
    Dim lngLastRow        As Long
    Dim lngLooper         As Long
    Dim strWbVersion      As String
    Dim wbkData           As Workbook
    Dim wksFrom           As Worksheet
    Dim wbkTarget         As Workbook
    Dim wksWorkOn         As Worksheet
  
             
    Const cstrPath        As String = "C:\Users\nhicks\Documents\Ratings\Saved Versions\"
    Const cstrWbData      As String = "WAPA-UGPR Facility Rating and SOL Record (Master).xlsm"
    Const cstrShData      As String = "Line Update"
    Const cstrStFileName  As String = "WAPA-UGPR Facility Rating and SOL Record (Data File)_v"
    Const cstrShFacility  As String = "Facility Ratings & SOLs (Lines)"
  
  GetWorkbook_Worksheet cstrPath, cstrWbData, wbkData, cstrShData, wksFrom

  If wbkData Is Nothing Then
    MsgBox "No Object set for '" & cstrWbData & "'. ", vbInformation, cstrMsgTitle
    blnEnd = True
    GoTo end_here
  End If
  If wksFrom Is Nothing Then
    MsgBox "No Object set for '" & cstrShData & "'. ", vbInformation, cstrMsgTitle
    blnEnd = True
    GoTo end_here
  End If
'/// will find any xls, xlsb, xlsx or xlsm workbook that start with cstrStFileName
'/// and should deliver the highest number from there
    strWbVersion = HighestVersion(cstrPath, ".xlsm", cstrStFileName)
        If Len(strWbVersion) = 0 Then
            MsgBox "Could not spot a version of " & vbCrLf & cstrStFileName & _
            vbCrLf & "in Path " & cstrPath, vbInformation, cstrMsgTitle
            blnEnd = True
            GoTo end_here
        End If
        
    GetWorkbook_Worksheet cstrPath, strWbVersion, wbkTarget, cstrShFacility, wksWorkOn

    If wbkTarget Is Nothing Then
        MsgBox "No Object set for '" & cstrWbData & "'. ", vbInformation, cstrMsgTitle
        blnEnd = True
        GoTo end_here
    End If
    If wksWorkOn Is Nothing Then
        MsgBox "No Object set for '" & cstrShData & "'. ", vbInformation, cstrMsgTitle
        blnEnd = True
        GoTo end_here
    End If

  Application.ScreenUpdating = False
  
    With ShFacility
        AutoFilterMode = False   'Turn Off Autofilters
        Range("A2:AP186").Font.Color = vbBlack  'Change all Font back to black
        Range("A2:AP186").ClearFormats  'Removes all previous highlighting
        Columns("A:AP").EntireColumn.Hidden = False
    
        
            
        With wksWorkOn
            Range("D8:D23").ClearContents
            Range("L11:M18").ClearContents
            Range("O11:P18").ClearContents
            Range("D4:D7").ClearContents
            Range("C11:C18").ClearContents
            Range("J11:J18").ClearContents
            Range("C32:G39").ClearContents
            MsgBox "All previous data cleared and unfiltered. Please enter your Substation and Transformer ID"
            
end_here:
  Workbook_Worksheet2Nothing wbkTarget, wksWorkOn
  Workbook_Worksheet2Nothing wbkData, wksFrom
  If blnEnd Then End
  
        End With
    End With
    

End Sub

 

[DanteAmor]

You need to put the dots in each of these lines, that way you will be referencing the sheet of the With structure.
Try and comment:

    With ShFacility
        .AutoFilterMode = False   'Turn Off Autofilters
        .Range("A2:AP186").Font.Color = vbBlack  'Change all Font back to black
        .Range("A2:AP186").ClearFormats  'Removes all previous highlighting
        .Columns("A:AP").EntireColumn.Hidden = False
           
        With wksWorkOn
            .Range("D8:D23").ClearContents
            .Range("L11:M18").ClearContents
            .Range("O11:P18").ClearContents
            .Range("D4:D7").ClearContents
            .Range("C11:C18").ClearContents
            .Range("J11:J18").ClearContents
            .Range("C32:G39").ClearContents
            MsgBox "All previous data cleared and unfiltered. Please enter your Substation and Transformer ID"
           
end_here:
  Workbook_Worksheet2Nothing wbkTarget, wksWorkOn
  Workbook_Worksheet2Nothing wbkData, wksFrom
  If blnEnd Then End
 
        End With
    End With

 

[DanteAmor]

.Range("A2:AP186").ClearFormats 'Removes all previous highlighting

ClearFormats , removes all formats, you may need to remove only the format you require.