black border

Trevor3007

Active Member
Joined
Jan 26, 2017
Messages
493
hi,

i have the following code:-

Code:
Sub FormatRange()    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    With Range(Cells(1, 1), Cells(LastRow, lCol))
        .Rows.RowHeight = 20
        .Columns.ColumnWidth = 14
        .Font.Name = "Calibri"
        .Font.Size = 20
        .Borders.LineStyle = xlDouble
    End With
    Application.ScreenUpdating = True
End Sub

how do I make the 'border's black (please )?

MTIA
Trevor3007
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,533
Office Version
365, 2010
Platform
Windows, Mobile
Maybe..

Code:
Sub FormatRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    With Range(Cells(1, 1), Cells(LastRow, lCol))
        .Rows.RowHeight = 20
        .Columns.ColumnWidth = 14
        .Font.Name = "Calibri"
        .Font.Size = 20
        With .Borders
            .LineStyle = xlDouble
            .ColorIndex = 0
            .TintAndShade = 0
        End With
    End With
    Application.ScreenUpdating = True
End Sub
If not try changing the ColorIndex to 1
 

Trevor3007

Active Member
Joined
Jan 26, 2017
Messages
493
Maybe..

Code:
Sub FormatRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    With Range(Cells(1, 1), Cells(LastRow, lCol))
        .Rows.RowHeight = 20
        .Columns.ColumnWidth = 14
        .Font.Name = "Calibri"
        .Font.Size = 20
        With .Borders
            .LineStyle = xlDouble
            .ColorIndex = 0
            .TintAndShade = 0
        End With
    End With
    Application.ScreenUpdating = True
End Sub
If not try changing the ColorIndex to 1


morning MARK858 & thanks ,

works fine, just wondered is it possible to restrict it to only work in the range:-
A3-k7999?

MTIA
Trevor3007
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,533
Office Version
365, 2010
Platform
Windows, Mobile
Just change your With statement from

Code:
With Range(Cells(1, 1), Cells(LastRow, lCol))
to
Code:
With Range("A3:K7999")
And do away with the code below

Code:
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
 
Last edited:

Trevor3007

Active Member
Joined
Jan 26, 2017
Messages
493
hi,

Thanks again. It works but it 'unhides' my 'hidden' columns????

KR
Trevor3007
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,533
Office Version
365, 2010
Platform
Windows, Mobile
Try...

Code:
With Range("A3:K7999").SpecialCells(12)
 

Trevor3007

Active Member
Joined
Jan 26, 2017
Messages
493
GREAT!!! works treat.

is it possible that it will only grid the the next 'ungrided' line or lines ?


So for example a3- k150 are grided, but I add data into a151-k155 . I need to grid a3-k155?
Hoping this makes sense?

KR
Trevor3007
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,533
Office Version
365, 2010
Platform
Windows, Mobile
How are you adding the data? manually,by code or formula?
 

Trevor3007

Active Member
Joined
Jan 26, 2017
Messages
493
Hi Mark,

thanks again fo your help.
apart from a vlookup , the odd 'if' statement all other is added manually.


I had a look search and found this:-
Sub XYZ()
ActiveSheet.UsedRange.Borders.Weight = xlThick
End Sub

could you sorted so it only does the applicbale range & keeep the hidden cols 'hidden'?
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,533
Office Version
365, 2010
Platform
Windows, Mobile
It is not the borders code that makes your columns visible, it is the .Columns.ColumnWidth = 14 in your other code that makes them visible.
 

Forum statistics

Threads
1,082,585
Messages
5,366,466
Members
400,892
Latest member
lamarh755

Some videos you may like

This Week's Hot Topics

Top