Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 2000 (SP1)

    How do I code for a multiple worksheet column check?
    Example: 15 columns and 14 rows on worksheet1
    29 columns and 100 rows on worksheet2
    Both worksheets have a the word 'Red' in column 15 on worksheet1 and column 29 on worksheet2 to indicate which rows need highlighted. Worksheet1 would highlight column 1 thru 14 while worksheet2
    would highlight columns 1 thru 28. The rows on both worksheets are not static each week -- so the rows to be marked on each worksheet are variable and random. Worksheet1 might have rows 2,7,11 and worksheet2 might have 29,31,56,89 this week and the next week the row marking for "Red" would be on different rows.

    Any help here?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 (SP1)

    Can you tell us what you mean by "highlight?" Do you want those cells selected? Do you want the font turned red? Do you want the font Bold? Exactly what are you trying to do? If you don't mean selected, then do you want a VBA solution, or would it be OK to use conditional formatting?
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Apr 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 (SP1)

    All the cells to the left need the font bold and red and boxed as a group.
    Would a VBA solution work best for multi worksheets?
    I don't think conditional formatting would work.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 (SP1)

    If I understand what you want to do correctly, the attached workbook does this using conditional formatting.
    Attached Files Attached Files
    Legare Coleman

  5. #5
    New Lounger
    Join Date
    Apr 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 (SP1)

    Conditional formatting will not work.
    There are 26 workbooks each week.
    Each workbook has the "RED" notation ...but no dependable pattern of placement on set rows that the conditional formatting would be good for.
    Because of the sheer number of workbooks and the changing placement of the word "Red'
    amid the rows of each multi-sheet workbook is there a macro or VBA like you suggested that would work?

    Also, the columns prior to the "RED" trigger column need to be outlined as one box - meaning all the cells do not have an indivual box around it -- more like a heading label box---meaning grouped as one.
    Help! -Toots

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 (SP1)

    Some code something like this should do what you want:

    <pre>Public Sub FormatRed()
    Dim I As Long, lLastRow As Long
    lLastRow = Worksheets("Sheet1").UsedRange.Rows.Count
    For I = 0 To lLastRow - 1
    If Worksheets("Sheet1").Range("N1").Offset(I, 0).Value = "Red" Then
    With Worksheets("Sheet1").Range(Range("A1").Offset(I, 0), Range("M1").Offset(I, 0)).Font
    .ColorIndex = 3
    .Bold = True
    End With
    End If
    Next I
    End Sub
    </pre>

    Legare Coleman

  7. #7
    New Lounger
    Join Date
    Apr 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 (SP1)

    I can understand what it is doing -- thankyou wiz!
    Now-
    Do I add another loop to to do this routine to account for all the possible worksheets in the workbook?
    Am I on the right track?
    Can/Should I call this routine from another routine which could control the looping through all possible worksheets in the workbook?
    Is this possible?

    NewbieTootsie

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 (SP1)

    If you want to go through all sheets in a workbook, and check for both column 14 and 29, you could use something like this:

    <pre>Public Sub FormatRed()
    Dim I As Long, lLastRow As Long
    Dim oSheet As Worksheet
    For Each oSheet In Worksheets
    lLastRow = Worksheets("Sheet1").UsedRange.Rows.Count
    For I = 0 To lLastRow - 1
    If oSheet.Range("N1").Offset(I, 0).Value = "Red" Then
    With oSheet.Range(oSheet.Range("A1").Offset(I, 0), oSheet.Range("M1").Offset(I, 0)).Font
    .ColorIndex = 3
    .Bold = True
    End With
    Else
    If oSheet.Range("AC1").Offset(I, 0).Value = "Red" Then
    With oSheet.Range(oSheet.Range("A1").Offset(I, 0), oSheet.Range("AB1").Offset(I, 0)).Font
    .ColorIndex = 3
    .Bold = True
    End With
    End If
    End If
    Next I
    Next oSheet
    End Sub
    </pre>

    Legare Coleman

  9. #9
    New Lounger
    Join Date
    Apr 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 (SP1)

    Thank you soooo much for your insight.
    I have one last piece ---I recorded a new macro (to box outline each cell) and inserted into the loop.
    It was to box each offset cell -- it did not work...could I ask for just one more example
    on how to add boxing the cells involved in the offset -- 1 thru 13 on worksheet1
    and 1 thru 28 on worksheet2 where the 'RED" is marked for trigger.

    I am humble by the ease that you work through submitted problems of newbies and experienced alike...(I did scroll through the forum to see if another person had a similar propblem..found none.
    ThankYou Whiz!

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 (SP1)

    Try something like this:

    <pre>Public Sub FormatRed()
    Dim I As Long, lLastRow As Long
    Dim oSheet As Worksheet
    For Each oSheet In Worksheets
    lLastRow = Worksheets("Sheet1").UsedRange.Rows.Count
    For I = 0 To lLastRow - 1
    If oSheet.Range("N1").Offset(I, 0).Value = "Red" Then
    With oSheet.Range(oSheet.Range("A1").Offset(I, 0), oSheet.Range("M1").Offset(I, 0))
    .Font.ColorIndex = 3
    .Font.Bold = True
    .Borders(xlEdgeBottom).Weight = xlMedium
    .Borders(xlEdgeLeft).Weight = xlMedium
    .Borders(xlEdgeTop).Weight = xlMedium
    .Borders(xlEdgeRight).Weight = xlMedium
    .Borders(xlInsideVertical).Weight = xlMedium
    .Borders(xlEdgeBottom).ColorIndex = 3
    .Borders(xlEdgeLeft).ColorIndex = 3
    .Borders(xlEdgeTop).ColorIndex = 3
    .Borders(xlEdgeRight).ColorIndex = 3
    .Borders(xlInsideVertical).ColorIndex = 3
    End With
    Else
    If oSheet.Range("AC1").Offset(I, 0).Value = "Red" Then
    With oSheet.Range(oSheet.Range("A1").Offset(I, 0), oSheet.Range("AB1").Offset(I, 0))
    .Font.ColorIndex = 3
    .Font.Bold = True
    .Borders(xlEdgeBottom).Weight = xlMedium
    .Borders(xlEdgeLeft).Weight = xlMedium
    .Borders(xlEdgeTop).Weight = xlMedium
    .Borders(xlEdgeRight).Weight = xlMedium
    .Borders(xlInsideVertical).Weight = xlMedium
    .Borders(xlEdgeBottom).ColorIndex = 3
    .Borders(xlEdgeLeft).ColorIndex = 3
    .Borders(xlEdgeTop).ColorIndex = 3
    .Borders(xlEdgeRight).ColorIndex = 3
    .Borders(xlInsideVertical).ColorIndex = 3
    End With
    End If
    End If
    Next I
    Next oSheet
    End Sub
    </pre>

    Legare Coleman

  11. #11
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 (SP1)

    Using Legare's code you could modify the area where the border properites are given to something like this.

    <pre>Dim side


    For Each side In Array(xlEdgeBottom, xlEdgeLeft, xlEdgeTop, xlEdgeRight, xlInsideVertical)
    .Borders.Weight = xlMedium
    .Borders.ColorIndex = 3
    Next side
    .Font.ColorIndex = 3
    .Font.Bold = True

    </pre>


Posting Permissions

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