VBA Formatting Rows

[timmy95308]

I'm struggling to write a VBA code that will put the outside border around my rows from A:F but the kicker is it needs to include a row below(or above) if the order number or customer name is the same below(or above).

I can't use conditional formatting as this is a sheet that usually has around 400 rows.

Ideally the code could also throw in any value over 1 (in column C) fill colour as yellow and any cell with the text IOSS or GIFT would also fill colour yellow.

TIA

 

[dreid1011]

And why can't conditional formatting be used on 400 rows?

 

[timmy95308]

And why can't conditional formatting be used on 400 rows?

Because its 400 rows on 10 sheets being generated everyday. So it needs to be quick.

 

[Joe4]

Welcome to the Board!

I can't use conditional formatting as this is a sheet that usually has around 400 rows.

400 rows is not that big. Conditional Formatting would have no problem handling that. I use a CF formula like:

=$A2<>$A1

and it worked perfectly for me.

 

[timmy95308]

J

Welcome to the Board!


400 rows is not that big. Conditional Formatting would have no problem handling that. I use a CF formula like:

=$A2<>$A1

and it worked perfectly for me.

Just to reiterate, looking for VBA not conditional formatting.

 

[timmy95308]

Are you adding 10 sheets to the current workbook everyday (so that it goes from 10, to 20, to 30, etc) pages?
Or is it a new workbook every day with 10 sheets?

If your workbook only has 10 sheets, I still do not think this should be an issue.
I would recommend creating a pre-built template that you can re-use.

There are 10 new workbooks a day, these would be using the vba template that I've already written plus the coding.

 

[Joe4]

Because its 400 rows on 10 sheets being generated everyday. So it needs to be quick.

Those are some important details that you might want to mention in your initial post!
Remember, we know NOTHING about your project, other than what you tell us.
So please be sure to include ALL important details that might affect this.

Because its 400 rows on 10 sheets being generated everyday

There are 10 new workbooks a day

OK, slow down a bit with the rapid fire responses, and take the time to clearly communicate exactly all that you have going on here.
Details matter, and it is important to be concise and clear.

Is it 10 sheets, or 10 workbooks (they are NOT the same thing)?
If 10 workbooks, how many sheets are in each workbook?

 

[dreid1011]

Give this a try... and make sure you update the sheet names accordingly: (this is only for one sheet by the way... waiting for further response to Joe's questions about multiple sheets within multiple books, or just multiple books with single sheets ect...)

Sub Borders()
Dim i As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Application.ScreenUpdating = False
For i = 2 To ws.Range("A" & Rows.Count).End(xlUp).Row
    'Top Border
    If ws.Range("A" & i).Value <> ws.Range("A" & i - 1).Value Then
        With ws.Range("A" & i & ":F" & i).Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .Color = vbBlack
        End With
        'Left Border
        With ws.Range("A" & i).Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .Color = vbBlack
        End With
        'Right Border
        With ws.Range("F" & i).Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .Color = vbBlack
        End With
    End If
    'Bottom Border
    If ws.Range("A" & i).Value <> ws.Range("A" & i + 1).Value Then
        With ws.Range("A" & i & ":F" & i).Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .Color = vbBlack
        End With
        'Left Border
        With ws.Range("A" & i).Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .Color = vbBlack
        End With
        'Right Border
        With ws.Range("F" & i).Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .Color = vbBlack
        End With
    End If
    'Middle Borders
    If ws.Range("A" & i).Value = ws.Range("A" & i + 1).Value And ws.Range("A" & i).Value = ws.Range("A" & i - 1).Value Then
        'Left Border
        With ws.Range("A" & i).Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .Color = vbBlack
        End With
        'Right Border
        With ws.Range("F" & i).Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .Color = vbBlack
        End With
    End If
    If ws.Range("C" & i).Value > 1 Then
        ws.Range("C" & i).Interior.Color = vbYellow
    End If
    If InStr(1, ws.Range("E" & i).Value, "IOSS", vbTextCompare) Or InStr(1, ws.Range("E" & i).Value, "GIFT", vbTextCompare) Then
        ws.Range("E" & i).Interior.Color = vbYellow
    End If
Next
Application.ScreenUpdating = True
End Sub