dBforums - View Single Post

Colin Legg · #11 (**permalink**) 09-02-10, 13:33

Try this as a basis for correcting your procedure. I've tried to use your naming convention style as best as I can and, on review, it seems that the code was formatting almost everything in the cell the same except for the top border colorindex, so I shortened it a touch.

Code:

    'additional procedural declarations
    Dim xlsRange As Excel.Range
    Dim xlsLastRow As Long
    Dim r As Long

Code:

 
    Set xlsSheet = xlsWorkbook.Worksheets(1)
 
    'find the last used cell in column J
    xlsLastRow = xlsSheet.Range("J" & xlsSheet.Rows.Count).End(xlUp).Row
 
    For r = 4 To xlsLastRow
        Set xlsRange = xlsSheet.Cells(r, "J")
 
        With xlsRange
            If .Value2 = "Y" Then
 
                .Interior.ColorIndex = 33
                .Font.ColorIndex = 33
 
                With .Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .Weight = xlThin
 
                    If VBA.Right$(xlsRange.Offset(, -8).Value2, 1) = "O" Then
                        .ColorIndex = xlAutomatic
                    Else
                        .ColorIndex = 15
                    End If
                End With
            End If
        End With
    Next r
 
    'example clean-up code:
    Set xlsRange = Nothing
    Set xlsSheet = Nothing
 
    xlsWorkbook.Save
    xlsWorkbook.Close False
    Set xlsWorkbook = Nothing
 
    xlsApp.Quit
    Set xlsApp = Nothing