Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Monster Loop (2003)

    Hi,

    I am trying to create what could be a monster loop. I have a worksheet 'Master Output' that I would like to script the following? Providing there is data (value) in column I (worksheet runs A-L with any number of rows) >>> If J starts with 11, turn row font colour red. If J starts 80, turn row font colour blue. Any cells in F,G or H equal to blank or zero, add text 'No Data Found'.

    I am having a go at the minute but am not getting there very fast. I think I may be attempting the impossible? I'm not sure that this is achievable in one??

    Any pointers appreciated!

    Thanks
    Nath

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monster Loop (2003)

    First pointer would be to attach a spreadsheet with before and expected results. It reduces the assumption factor.

    Does this do what you are looking for?


    Sub woody()
    Dim i As Long, lRow As Long, jStr As String, iColor As Integer
    lRow = Cells(60000, 10).End(xlUp).Row

    For i = 2 To lRow

    If Cells(i, 6) = "" Or Cells(i, 6) = 0 Then
    Cells(i, 6) = "No Data Found"
    End If

    If Cells(i, 7) = "" Or Cells(i, 7) = 0 Then
    Cells(i, 7) = "No Data Found"
    End If

    If Cells(i, 8) = "" Or Cells(i, 8) = 0 Then
    Cells(i, 8) = "No Data Found"
    End If

    jStr = Left(Cells(i, 10), 2)
    Select Case jStr

    Case "11"
    iColor = 3
    Case "80"
    iColor = 5
    Case Else
    iColor = Cells(i, 10).Font.ColorIndex
    End Select


    Cells(i, 10).Font.ColorIndex = iColor


    Next



    End Sub

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monster Loop (2003)

    It does, yes thanks. Only, I need the whole rows to change font colour based on 11 and 80 in J. Is that possible?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Monster Loop (2003)

    Use:
    Rows(i).Font.ColorIndex = iColor

    Instead of
    Cells(i,10).Font.ColorIndex = iColor

    Steve

  5. #5

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monster Loop (2003)

    Using your code, I have also added:

    Sub Format2()

    Dim i As Long, lRow As Long, jStr2 As String, iColor As Integer
    lRow = Cells(60000, 9).End(xlUp).Row

    Sheets("Rejections 2").Select

    For i = 2 To lRow
    jStr2 = Cells(i, 1)
    Select Case jStr2

    Case "PURGED ITEMS"
    iColor = 7
    Case Else
    iColor = Cells(i, 11).Font.ColorIndex
    End Select


    Rows(i).Font.ColorIndex = iColor


    Next

    End Sub

    This turns the rows with "Purged Items" to font pink. What I want it to do is turn the cells within A-K to fill yellow. I changed font to fill but that did'nt work, but also I dont want to affect the entire rows.

    Help please?

    Thanks
    Nath

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Monster Loop (2003)

    Use:
    iColor = <font color=red>6</font color=red> 'Yellow

    iColor = Cells(i, 11).<font color=red>Interior</font color=red>.ColorIndex

    <font color=red>Range(Cells(i, 1), Cells(i, 11)).Interior</font color=red>.ColorIndex = iColor

    Steve

  8. #8

Posting Permissions

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