black border

[Trevor3007]

hi,

i have the following 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

 

[MARK858]

Maybe..

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]

Maybe..

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]

Just change your With statement from

With Range(Cells(1, 1), Cells(LastRow, lCol))

to

With Range("A3:K7999")

And do away with the code below

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

 

[Trevor3007]

hi,

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

KR
Trevor3007

 

[MARK858]

Try...

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

 

[Trevor3007]

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]

How are you adding the data? manually,by code or formula?

 

[Trevor3007]

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'?

 

[MARK858]

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.