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

    loop and format (2002)

    Help..... I know roughly what I am doing <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>[keep laughing], but am a little stuck...

    Dim x As Long
    Dim z As Range
    Dim rg1 As Range

    x = Cells(Rows.Count, 1).End(xlUp).Row

    Set rg1 = ws1.Range("H2:H" & x)

    For Each z In rg1 (that ="XYZ")
    z.Offset(0, -7) FORMAT CELL FILL TO RED
    Next z

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: loop and format (2002)

    Nathan

    Not all the variables have been declared as it stands, ws1, z? Can you provide a little more code or is this what you have so far? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Jerry

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

    Re: loop and format (2002)

    ws1 is the worksheet, z is each cell within rg1. Loop through each cell of rg1 looking for XYZ, when found, format red to offset -7.

    Hope I make sense?

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: loop and format (2002)

    Try this:

    Sub FillRange()


    Dim x As Long
    Dim z As Range
    Dim rg1 As Range

    x = Cells(Rows.Count, 1).End(xlUp).Row

    Set rg1 = Range("H2:H" & x)


    For Each z In rg1
    If z.Value = "XYZ" Then

    z.Offset(0, -7).Interior.ColorIndex = 3

    End If
    Next z


    End Sub
    Jerry

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

    Re: loop and format (2002)

    Thanks! It's the big words that I have problems with, like......... IF.

Posting Permissions

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