Results 1 to 6 of 6

Thread: VBA not working on both sheets

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Contributor
    Joined
    Mar 2009
    Location
    Indiana
    Posts
    113
    Location

    VBA not working on both sheets

    Hello, I have the following code:

    [vba]
    Option Explicit
    Sub SortAndColor()
    Dim LastRow As Long, Rw As Long, a As Long
    Dim MySheets
    MySheets = Array("Master", "Seaport-e")
    For a = LBound(MySheets) To UBound(MySheets)
    Sheets(MySheets(a)).Select
    LastRow = Cells(Rows.Count, 7).End(xlUp).Row
    For Rw = LastRow To 2 Step -1
    If Cells(Rw, 7).Value <= "09" Or InStr(Cells(Rw, 39).Value, "*If chosen") > 0 Then
    Cells(Rw, 1).EntireRow.Delete
    Else
    Select Case Cells(Rw, 39).Value
    Case "Contract Awarded"
    Rows(Rw).Interior.ColorIndex = 35
    Rows(Rw).Font.ColorIndex = 1
    Rows(Rw).Borders.LineStyle = xlContinuous
    Case "Part A Held"
    Rows(Rw).Interior.ColorIndex = 34
    Rows(Rw).Font.ColorIndex = 1
    Rows(Rw).Borders.LineStyle = xlContinuous
    Case "Part B Accepted"
    Rows(Rw).Interior.ColorIndex = 38
    Rows(Rw).Font.ColorIndex = 1
    Rows(Rw).Borders.LineStyle = xlContinuous
    Case "Part B Submitted"
    Rows(Rw).Interior.ColorIndex = 36
    Rows(Rw).Font.ColorIndex = 1
    Rows(Rw).Borders.LineStyle = xlContinuous
    Case "Planning"
    Rows(Rw).Interior.ColorIndex = 2
    Rows(Rw).Font.ColorIndex = 1
    Rows(Rw).Borders.LineStyle = xlContinuous
    Case "Postponed"
    Rows(Rw).Interior.ColorIndex = 39
    Rows(Rw).Font.ColorIndex = 1
    Rows(Rw).Borders.LineStyle = xlContinuous
    End Select
    End If
    Next
    Next a
    End Sub
    [/vba]

    The first two sheets of the workbook are named "Master" and "Seaport-e". The code runs through and does all of the color coding on the sheet called "Master" but does nothing on the "Seaport-e" one. I do not recieve any errors. Any help would be appreciated.

    Thanks
    Last edited by Bob Phillips; 06-01-2010 at 11:03 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •