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

metzgek

New Member
Joined
Nov 2, 2011
Messages
6
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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

rneame

New Member
Joined
Dec 8, 2011
Messages
3
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
...
 

Watch MrExcel Video

Forum statistics

Threads
1,123,535
Messages
5,602,217
Members
414,513
Latest member
junbuggle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top