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

 

[Trevor3007]

morning

thanks for your tip. Just noticed a issue, because of the 'if statement' & 'data validation it 'grides' the whole 9 yards (a3:k8000) . So is it possible only to 'grid the areas that only contain a full row of 'data'? in the specified area?

Examples:-

data placed into A150-k150 - 1 row is 'boxed'
data placed into a150-k160 - 10 rows are boxed'

Is this Mission Impossible?

Fingers crossed you can solve...but I am very grateful for all the help you have given me.

 

[MARK858]

Just a reply as all the posts aren't showing on my phone.

 

[Trevor3007]

ok....

 

[MARK858]

Sub BorderRange()
    Dim i As Long
    Application.ScreenUpdating = False
    For i = 3 To 8000
        If WorksheetFunction.CountA(Cells(i, 1).Resize(1, 11)) = 11 Then
            With Cells(i, 1).Resize(1, 11).SpecialCells(12).Borders
                .LineStyle = xlDouble
                .ColorIndex = 0
                .TintAndShade = 0
            End With
        End If
    Next
    Application.ScreenUpdating = True
End Sub

 

[Trevor3007]

thanks ,

i tested & it works GREAT..

however, I need to run it in the real world, which wont be until 28/5/29. I will get back to you & advise accordingly.
Have a great weekend and thank you for your help!!!

 

[Trevor3007]

Hi Mark,

well in my real world it dont work ???? Below is what I currently use:-

Private Sub Worksheet_Change(ByVal Target As Range)    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        If Target.Count > 1000 Then Exit Sub
        For Each c In Target
            If LCase(c.Offset(0, 1).Value) = LCase("Matched Assets") Then
                Range("A" & c.Row & ":k" & c.Row).Interior.ColorIndex = 24
            Else
                Range("A" & c.Row & ":k" & c.Row).Interior.ColorIndex = xlNone
            End If
        Next
    End If


If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub






    On Error Resume Next


    If Not Intersect(Target, Range("A3:k9000")) Is Nothing Then


        Application.EnableEvents = False


        Target = UCase(Target)


        Application.EnableEvents = True


    End If
      


    On Error GoTo 0




If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub






    On Error Resume Next


    If Not Intersect(Target, Range("nj6:nj12")) Is Nothing Then


        Application.EnableEvents = False


        Target = StrConv(Target, vbProperCase)


        Application.EnableEvents = True


   








    End If


       
    
    
    
    
    
   
End Sub

Any chance you could sort ?

My headache started as I awoke...since getting to work its now a migraine!!!!!

MTIA
Trevor3007

 

[MARK858]

Any chance you could sort ?

Not without you explaining exactly what "don't work" means. We need to know exactly what does and doesn't happen, how you added it to the code and what you want it to do (especially as what you have posted is event code).
I am working 4 twelve hour days this week so it is doubtful that I will look at it before Friday.