Run-time error 1004 - Unable to set the LineStyle property of the Border Class - HELP!!

[metzgek]

I have a spreadsheet that has many a lot of conditional formatting to identify fields that need to be completed as well as some flagged items. Since end users occasionally paste into the sheet, I want to develop a macro that they can run that will undo all of the conditional formatting that may be carried over from their paste, as well as to ensure that the conditional formatting already in the sheet is not altered. I have developed the following code, but when it runs, I get a run-time 1004 error "Unable to set the LineStyle property of the Border Class. Can anyone see an issue here? I bolded the line where I get the error.

Note that the spreadsheet is protected, but the code's first act (not shown here) is to unlock the spreadsheet.

Please help!! This issue is driving me crazy!!

'CLEAR FORMATTING

Cells.FormatConditions.Delete


'CONDITIONAL FORMATTING CORRECTIONS

Range("K6:K1000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(NOT($B6=""""),$K6="""")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Range("J6:J1000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(NOT($B6=""""),$J6="""")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Range("F6:F1000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(NOT($B6=""""),$F6="""")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Range("D6:D1000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(NOT($B6=""""),$D6="""")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Range("H6:H1000,Q6:Q1000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(NOT($B6=""""),$H6="""")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Range("N6:N1000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(NOT($B6=""""),$N6="""")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Range("G6:G1000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(NOT($B6=""""),$G6="""")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Range("A6:A1000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(NOT($B6=""""),$A6="""")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Range("E6:E1000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(NOT($B6=""""),$E6="""")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Range("M6:M1000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(NOT($B6=""""),$M6="""")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Range("L6:L1000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(NOT($B6=""""),$L6="""")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Range("C6:C1000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(NOT($B6=""""),$C6="""")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Range("I6:I1000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(NOT($B6=""""),$I6="""")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Range("Q6:Q1000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(NOT(H6=""""),Q6>0)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("K6:L1000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($K6=""Not Started"",$L6>0,Today()>$L6)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("K6:K1000,M6:M1000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($K6=""Reporting"",$M6>0,Today()>$M6)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("B6:B1000").Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("C6:C1000").Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

Range("B6:O1000,Q6:Q1000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NOT($B6="""")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
Selection.FormatConditions(1).StopIfTrue = False

 

[rneame]

I've seen this many times but I don't know why Excel behaves this way. The error is not consistent across Excel versions but does appear to be related to the complexity or history of cell border formating on the sheet.

This workaround usually works for me.
...
.LineStyle = xlNone
.LineStyle = xlContinuous
...