grid a range

[Trevor3007]

Good evening,

A worksheet I am working on ( only 100+ rows ATM but will grow to 8000!)

Range is a1:k8000, can someone sort some VB so when 'data' is in entered into any cell within the range it will grid the complete range & when the data is removed, grid reforms accordingly,
fomat:-
font size 14 & Calibri
bottom align
left align

some challenge guanlet ? but hoping some kind 'sir knight of code' can sort a poor user in distress!

KR
Trevor3007

 

[DanteAmor]

Start with this and tell me.

Put the code in the events of your sheet

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:K")) Is Nothing Then
        If Target.Count > 1000 Then Exit Sub
        For Each c In Target
            If c.Value = "" Then
                c.Borders.LineStyle = xlNone
                c.Font.Name = "Calibri"
                c.Font.Size = 14
                c.HorizontalAlignment = xlLeft
                c.VerticalAlignment = xlBottom
            Else
                c.Borders.LineStyle = xlContinuous
            End If
        Next
    End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[Trevor3007]

Start with this and tell me.

Put the code in the events of your sheet

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:K")) Is Nothing Then
        If Target.Count > 1000 Then Exit Sub
        For Each c In Target
            If c.Value = "" Then
                c.Borders.LineStyle = xlNone
                c.Font.Name = "Calibri"
                c.Font.Size = 14
                c.HorizontalAlignment = xlLeft
                c.VerticalAlignment = xlBottom
            Else
                c.Borders.LineStyle = xlContinuous
            End If
        Next
    End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

heyhey...

was hoping u would 'popup' :]

works to a point. it should engulf the 'whole area:-

text is added into cell g50... grid should surround, a2:k50
if I add text into b345, then grid would be , a2:k345 etc...

great of you BTW :}

 

[DanteAmor]

heyhey...

was hoping u would 'popup' :]

works to a point. it should engulf the 'whole area:-

text is added into cell g50... grid should surround, a2:k50
if I add text into b345, then grid would be , a2:k345 etc...

great of you BTW :}

Let's try this to see what happens

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:K")) Is Nothing Then
        If Target.Count > 1000 Then Exit Sub
        For Each c In Target
            If c.Value = "" Then
                cuenta = WorksheetFunction.CountA(Range("A" & c.Row & ":K" & c.Row))
                If cuenta = 0 Then
                    With Range("A" & c.Row & ":K" & c.Row)
                        .Borders.LineStyle = xlNone
                        .Font.Name = "Calibri"
                        .Font.Size = 14
                        .HorizontalAlignment = xlLeft
                        .VerticalAlignment = xlBottom
                    End With
                End If
            Else
                Range("A2:K" & c.Row).Borders.LineStyle = xlContinuous
            End If
        Next
    End If
End Sub

 

[Trevor3007]

Let's try this to see what happens

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:K")) Is Nothing Then
        If Target.Count > 1000 Then Exit Sub
        For Each c In Target
            If c.Value = "" Then
                cuenta = WorksheetFunction.CountA(Range("A" & c.Row & ":K" & c.Row))
                If cuenta = 0 Then
                    With Range("A" & c.Row & ":K" & c.Row)
                        .Borders.LineStyle = xlNone
                        .Font.Name = "Calibri"
                        .Font.Size = 14
                        .HorizontalAlignment = xlLeft
                        .VerticalAlignment = xlBottom
                    End With
                End If
            Else
                Range("A2:K" & c.Row).Borders.LineStyle = xlContinuous
            End If
        Next
    End If
End Sub

hiya,

yeah that works thanks.... a tip my hat to you sir.

Have raised another ask BTW:}

MTA Trevor3007

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Trevor3007]

NP & hope you will solve my future request for 'HELP' again :}
adios

 

[DanteAmor]

Of course, if it is within my means, with pleasure.