Change Cell color error when re-running vba

MWhiteDesigns

Well-known Member
Joined
Nov 17, 2010
Messages
646
Office Version
  1. 2016
Platform
  1. Windows
Good morning,

The below code changes a range of cells color to white among some other things (may not be the cleanest way)

I run the code for the first time and it works fine. However If I run it again, I receive a few errors. All the below at different points.

1004: Application-defined or object defined error.
Unable to set the linestyle property of the borderclass

Why is this happening? I'm assuming i have some conflicting code. Any assistance in cleaning this up would be awesome.

Code:
Sheets("Production").Select

    Range("A1:Z3").Select
     
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    
    Selection.Borders.LineStyle = xlNone
    
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    
     With Worksheets("Production").Range("D1:Z1")
            .Borders.LineStyle = xlNone
        End With
        Columns("E:Z").Select
    Selection.ColumnWidth = 11
  
    With Selection
        .VerticalAlignment = xlCenter
        .HorizontalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    Range("D5:D12").Select


    Selection.InsertIndent 1
    
    Range("D4,D13").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
 
Last edited:

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
Probably, not really helpful but the above code runs without issue for me time and time again.
 
Upvote 0

Forum statistics

Threads
1,215,758
Messages
6,126,704
Members
449,331
Latest member
smckenzie2016

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
Back
Top