gheyman
Well-known Member
- Joined
- Nov 14, 2005
- Messages
- 2,341
- Office Version
- 365
- Platform
- Windows
I have a table where the user has the option to clear the table before they refresh it
The code works well but it screws with the conditional formatting the next time the table is refreshed.
Is there a way to avoid this or include proper code to reset the conditional formatting?
I tried adding this but its not working
Range("Search_Results").Select
Selection.ListObject.Range.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$B13<>$B12"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0.499984740745262
End With
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$W13<>$W12"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark2
.TintAndShade = -9.99481185338908E-02
End With
It gets changed to =$B13<>B1048526
Since this is a table I dont know what my last row is without add that additional code. Was wondering if there was another way before I identified the last row
Code:
Sub ClearSearchTable()
'Clear table
'Check to see if there is a tab named "Step 1a"
If Not Evaluate("isref(Search Results!C12)") Then
'Clear table - remove all rows except the first row
Application.ScreenUpdating = False
Sheets("Search Results").Select
ActiveSheet.ListObjects("Search_Results").HeaderRowRange.Select
'Remove the filters if one exists.
If ActiveSheet.FilterMode Then
Selection.AutoFilter
End If
'Clear all lines but the first one in the table leaving formulas for the next go round.
With Worksheets("Search Results").ListObjects("Search_Results")
.Range.AutoFilter
On Error Resume Next
.DataBodyRange.Offset(1).Resize(.DataBodyRange.Rows.Count - 2, .DataBodyRange.Columns.Count).Rows.Delete
If .ListColumns.Count > 1 Then
.DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
Else
With .DataBodyRange.Cells(1)
If Not .HasFormula Then .ClearContents
End With
End If
End With
'Clear Slicers
Selection.AutoFilter
ActiveWorkbook.SlicerCaches("Slicer_Material").RequireManualUpdate = False
Else
Exit Sub
End If
End Sub
The code works well but it screws with the conditional formatting the next time the table is refreshed.
Is there a way to avoid this or include proper code to reset the conditional formatting?
I tried adding this but its not working
Range("Search_Results").Select
Selection.ListObject.Range.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$B13<>$B12"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0.499984740745262
End With
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$W13<>$W12"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark2
.TintAndShade = -9.99481185338908E-02
End With
It gets changed to =$B13<>B1048526
Since this is a table I dont know what my last row is without add that additional code. Was wondering if there was another way before I identified the last row