Can you check my code please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
Hi,
The code is supplied below,
All was working spot on until the code in Red was added.
Basically i recorded a macro and took the coede from there and placed it here.
What is hould do is the following.
Range A4:E30 add All borders "thats it"
But whilst you are there i would like to also add the All borderds to this range here,i didnt get around to that part as the above keep stopping me.
Range also to be included,
D31:E31
C35:C37
E35:E37

Code:
Private Sub Worksheet_Activate()

    With Sheets("GRASS INCOME")
        .Range("A3") = UCase(Format(Now, "mmmm"))
        .Range("D3") = Year(Now)
    With .Range("A1:E3")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    With .Font
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .Name = "Calibri"
        .FontStyle = "Bold"
        .Size = 22
    End With
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlInsideVertical).LineStyle = xlContinuous
        .Borders(xlInsideHorizontal).LineStyle = xlContinuous
    With .Interior
        .Pattern = xlSolid
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
[COLOR=#ff0000]    With Sheets("GRASS INCOME")[/COLOR]
[COLOR=#ff0000]        .Range ("A4:E30")[/COLOR]
[COLOR=#ff0000]         Selection.Borders(xlDiagonalDown).LineStyle = xlNone[/COLOR]
[COLOR=#ff0000]         Selection.Borders(xlDiagonalUp).LineStyle = xlNone[/COLOR]
[COLOR=#ff0000]    With Selection.Borders(xlEdgeLeft)[/COLOR]
[COLOR=#ff0000]        .LineStyle = xlContinuous[/COLOR]
[COLOR=#ff0000]        .ColorIndex = 0[/COLOR]
[COLOR=#ff0000]        .TintAndShade = 0[/COLOR]
[COLOR=#ff0000]        .Weight = xlThin[/COLOR]
[COLOR=#ff0000]    End With[/COLOR]
[COLOR=#ff0000]        Range("A5").Select[/COLOR]
[COLOR=#ff0000]    End With[/COLOR]
[COLOR=#ff0000]    End With[/COLOR]
[COLOR=#ff0000]    End With[/COLOR]
[COLOR=#ff0000]    End With[/COLOR]
End Sub

Thanks very much and have a nice day
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
.
I broke it down into sections :

Code:
Private Sub Worksheet_Activate()


    With Sheets("GRASS INCOME")
        .Range("A3") = UCase(Format(Now, "mmmm"))
        .Range("D3") = Year(Now)
    End With
    
    With Sheets("GRASS INCOME").Range("A1:E3")
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlInsideVertical).LineStyle = xlContinuous
        .Borders(xlInsideHorizontal).LineStyle = xlContinuous
    End With
    
    With Sheets("GRASS INCOME").Range("A1:E3").Interior
        .Pattern = xlSolid
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With
    
     With Sheets("GRASS INCOME").Range("A1:E3").Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
     End With
    
    With Sheets("GRASS INCOME").Range("A1:E3")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    
    With Sheets("GRASS INCOME").Range("A4:E30")
         Selection.Borders(xlDiagonalDown).LineStyle = xlNone
         Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    End With
    
    With Sheets("GRASS INCOME").Range("A4:E30").Font '
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .Name = "Calibri"
        .FontStyle = "Bold"
        .Size = 22
    End With
    
    Range("A5").Select
    
End Sub
 
Upvote 0
Hi,
That didnt add any line borders etc.
But it did resize all the columns & unmerge 4 cells
 
Upvote 0
Sounds to me like all you need is ...

Code:
Private Sub Worksheet_Activate()
  Range("A3") = UCase(Format(Now, "mmmm"))
  Range("D3") = Year(Now)
  Range("A1:E3").HorizontalAlignment = xlCenter
  Range("A1:E3").VerticalAlignment = xlCenter
  Range("A1:E30, D31:E31, C35:C37, E35:E37").Borders.LineStyle = xlContinuous
  Range("A5").Select
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

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