Consulting

Results 1 to 6 of 6

Thread: VBA not working on both sheets

  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.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    John,

    I have just tried it on an example and it worked for both sheets.

    Can you post your workbook?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular
    Joined
    Aug 2010
    Posts
    36
    Location
    Try replacing
    [VBA]Dim MySheets[/VBA]
    to
    [VBA]Dim MySheets AS Worksheet[/VBA]
    in the For statement try
    [VBA]For Each Mysheets in ActiveWorkbook.Worksheets
    LastRow = Cells(Rows.Count, 7).End(xlUp).Row[/VBA]

  4. #4
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    Untested, but you need to refer explicitly to the sheet you're talking about (using Cells for example - cells of which sheet?).

    [vba]
    Option Explicit
    Sub SortAndColor()
    Dim LastRow As Long, Rw As Long, a As Long
    Dim MySheets() As Worksheet

    MySheets = Array("Master", "Seaport-e")

    For a = LBound(MySheets) To UBound(MySheets)
    With Sheets(MySheets(a))
    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
    Case "Part A Held": .Rows(Rw).Interior.ColorIndex = 34
    Case "Part B Accepted": .Rows(Rw).Interior.ColorIndex = 38
    Case "Part B Submitted": .Rows(Rw).Interior.ColorIndex = 36
    Case "Planning": .Rows(Rw).Interior.ColorIndex = 2
    Case "Postponed": .Rows(Rw).Interior.ColorIndex = 39
    End Select

    .Rows(Rw).Font.ColorIndex = 1
    .Rows(Rw).Borders.LineStyle = xlContinuous
    End If
    Next Rw
    End With
    Next a
    End Sub
    [/vba]

    We are what we repeatedly do. Excellence, therefore, is not an act but a habit.
    Aristotle

  5. #5
    VBAX Newbie
    Joined
    Aug 2010
    Posts
    5
    Location
    You may change the name Seaport-e to Seaporte. I hope it will work.

  6. #6
    VBAX Tutor mohanvijay's Avatar
    Joined
    Aug 2010
    Location
    MADURAI
    Posts
    268
    Location
    try loop through sheets like this

    for a = 1 to sheets.count
       sheets(a).activate
         'your code
    
    next a

Posting Permissions

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