Can code run faster

[leem]

I want to Thank you in advance for your vast knowledge and advice which has helped me out beyond belief.
Programming is not me because I don't understand all of it but can understand the basic...sort of. I was in need of coding that changed certain cells color and I was very happy with the code that was provided by davegugg. I took his code and added another sheet (MILES) and changed the color. Everything seems to be working just runs for alittle longer than I expected. This spreadsheet will be on a network which can be slow to the field so I'm just checking to see if there is a way to speed up the code? If not then I will run with it as it is.

the DR, FH,CL, MT tabs will never be more 3,000 rows
The LOA and Miles tabs will never have more than 300 rows

Private Sub Workbook_Open()
Dim i As Integer
Dim j As Integer
Dim lngLR As Long
Dim lngNumber As Long
Dim c As Object
Dim arrWS(4) As String 'Create an array with the sheet names to loop through

arrWS(0) = "DR"
arrWS(1) = "FH"
arrWS(2) = "CL"
arrWS(3) = "MT"
'Using an array of sheet names will help the code to not be dependent upon the order of the sheets, 'and will make it easy to add more sheets in the future, if necessary.

For i = 0 To 3
    lngLR = Sheets(arrWS(i)).Cells(Rows.Count, 1).End(xlUp).Row
    For j = 2 To lngLR
        lngNumber = Sheets(arrWS(i)).Cells(j, 1).Value
        For Each c In Sheets("LOA").UsedRange.Cells
            If c.Value = lngNumber Then
                c.Interior.ColorIndex = 16
                c.Font.Bold = True
                Sheets(arrWS(i)).Cells(j, 1).Interior.ColorIndex = 16
                Sheets(arrWS(i)).Cells(j, 1).Font.Bold = True
            End If
        Next c
    Next j
Next i

"This is the section I added but I really only need it to look through the DR tab"

For i = 0 To 3 
lngLR = Sheets(arrWS(i)).Cells(Rows.Count, 1).End(xlUp).Row
    For j = 2 To lngLR
        lngNumber = Sheets(arrWS(i)).Cells(j, 1).Value
        For Each c In Sheets("MILES").UsedRange.Cells
            If c.Value = lngNumber Then
                c.Interior.ColorIndex = 40
                c.Font.Bold = True
                Sheets(arrWS(i)).Cells(j, 1).Interior.ColorIndex = 40
                Sheets(arrWS(i)).Cells(j, 1).Font.Bold = True
            End If
        Next c
    Next j
Next i
End Sub

Thanks
Michelle

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[leem]

Didn't know about code tags but now I do...thank you.

I want to Thank you in advance for your vast knowledge and advice which has helped me out beyond belief.
Programming is not me because I don't understand all of it but can understand the basic...sort of. I was in need of coding that changed certain cells color and I was very happy with the code that was provided by davegugg. I took his code and added another sheet (MILES) and changed the color. Everything seems to be working just runs for alittle longer than I expected. This spreadsheet will be on a network which can be slow to the field so I'm just checking to see if there is a way to speed up the code? If not then I will run with it as it is.

the DR, FH,CL, MT tabs will never be more 3,000 rows
The LOA and Miles tabs will never have more than 300 rows

Private Sub Workbook_Open()
Dim i As Integer
Dim j As Integer
Dim lngLR As Long
Dim lngNumber As Long
Dim c As Object
Dim arrWS(4) As String 'Create an array with the sheet names to loop through

arrWS(0) = "DR"
arrWS(1) = "FH"
arrWS(2) = "CL"
arrWS(3) = "MT"
'Using an array of sheet names will help the code to not be dependent upon the order of the sheets, 'and will make it easy to add more sheets in the future, if necessary.

For i = 0 To 3
    lngLR = Sheets(arrWS(i)).Cells(Rows.Count, 1).End(xlUp).Row
    For j = 2 To lngLR
        lngNumber = Sheets(arrWS(i)).Cells(j, 1).Value
        For Each c In Sheets("LOA").UsedRange.Cells
            If c.Value = lngNumber Then
                c.Interior.ColorIndex = 16
                c.Font.Bold = True
                Sheets(arrWS(i)).Cells(j, 1).Interior.ColorIndex = 16
                Sheets(arrWS(i)).Cells(j, 1).Font.Bold = True
            End If
        Next c
    Next j
Next i

"This is the section I added but I really only need it to look through the DR tab"

For i = 0 To 3 
lngLR = Sheets(arrWS(i)).Cells(Rows.Count, 1).End(xlUp).Row
    For j = 2 To lngLR
        lngNumber = Sheets(arrWS(i)).Cells(j, 1).Value
        For Each c In Sheets("MILES").UsedRange.Cells
            If c.Value = lngNumber Then
                c.Interior.ColorIndex = 40
                c.Font.Bold = True
                Sheets(arrWS(i)).Cells(j, 1).Interior.ColorIndex = 40
                Sheets(arrWS(i)).Cells(j, 1).Font.Bold = True
            End If
        Next c
    Next j
Next i
End Sub

Thanks
Michelle

[shg]

Try this (completely untested):

Private Sub Workbook_Open()
    Dim v           As Variant
    Dim wks         As Worksheet

    For Each v In Array("DR", "FH", "CL", "MT")
        Set wks = Worksheets(v)
        AdHoc Intersect(wks.UsedRange, wks.Columns(1)), Worksheets("LOA").UsedRange
    Next v

    Set wks = Worksheets("DR")
    AdHoc Intersect(wks.UsedRange, wks.Columns(1)), Worksheets("MILES").UsedRange
End Sub

Sub AdHoc(rLookIn As Range, rLookFor As Range)
    ' rLookIn must be a vector range

    Dim cell        As Range
    Dim iRow        As Long

    On Error Resume Next

    For Each cell In rLookFor
        iRow = Application.Match(cell.Value, rLookIn, 0)
        If Err.Number Then
            Err.Clear
        Else
            cell.Interior.ColorIndex = 16
            cell.Font.Bold = True
            rLookIn.Cells(iRow).Interior.ColorIndex = 16
            rLookIn.Cells(iRow).Font.Bold = True
        End If
    Next cell
End Sub

[leem]

Thank you very much and yes it runs much faster. Awesome! I don't want to sound unappreciative but I was also trying to distinquish gray for those on LOA (this would bring attention to those on leave or restriction) and light orange for the MILES (this would bring attention to those that drove miles instead of working hours). Never would there be a conflict between LOA and MILES colors.

Thanks
Michelle