Consulting

Results 1 to 4 of 4

Thread: Solved:Looping a section of code

  1. #1
    VBAX Newbie
    Joined
    Jul 2011
    Posts
    3
    Location

    Solved:Looping a section of code

    Hi all,

    I must check some conditions on a row like that:

    [VBA]If Range("A1") = "x" And Range("B1") = "x" Then
    Range("M1,N1,O1).Interior.ColorIndex = 3
    ElseIf Range("C1") = "x" Then
    Range("M1,N1,O1").Interior.ColorIndex = 5
    Else: Range("M1,N1,O1").Interior.ColorIndex = 0
    [/VBA]
    I wouldn't like to repeat this code for each row (about 200) and changing manually cells value too!

    How can I figure out with more flexibility and elegance?

    Thanks
    Luca

  2. #2
    VBAX Expert CatDaddy's Avatar
    Joined
    Jun 2011
    Posts
    581
    Location
    [vba]
    Dim cell As Range
    Workbooks(1).Sheets(1).Activate
    Range("A1").Activate

    For Each cell In Range("A:A")

    If Cells(cell.Row,1).value = "x" And Cells(cell.Row,2).value = "x" Then
    Range("M1,N1,O1).Interior.ColorIndex = 3

    ElseIf Cells(cell.Row,3).value = "x" Then
    Range("M1,N1,O1").Interior.ColorIndex = 5
    Else
    Range("M1,N1,O1").Interior.ColorIndex = 0
    End If
    Next cell
    [/vba]

    something like this should work, i use something similar that i just tweaked to find empty cells in a range

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    Maybe something like this

    [vba]

    Public Sub Test()
    Call TestRange(Range("A1:C1"), Range("M1:O1"), 3, 5, 0)

    Call TestRange(Range("X1:Z1"), Range("M2:O2"), 3, 5, 0)

    'etc.
    End Sub

    Private Function TestRange(rng1 As Range, rng2 As Range, CI1 As Long, CI2 As Long, CI3 As Long)

    If rng1.Cells(1, 1).Value2 = "x" And rng1.Cells(1, 2).Value2 = "x" Then
    rng2.Interior.ColorIndex = CI1
    ElseIf rng1.Cells(1, 3).Value2 = "x" Then
    rng2.Interior.ColorIndex = CI2
    Else
    rng2.Interior.ColorIndex = CI3
    End If

    End Function[/vba]
    ____________________________________________
    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

  4. #4
    VBAX Newbie
    Joined
    Jul 2011
    Posts
    3
    Location
    Thanks guys!

    here "my" solution:

    [VBA]Dim Xcell As Range
    For Each Xcell In Range("A1:O3")
    If Cells(Xcell.Row, 1).Value = "x" And Cells(Xcell.Row, 2).Value = "x" Then
    Cells(Xcell.Row, 11).Interior.ColorIndex = 3
    ElseIf Cells(Xcell.Row, 3).Value = "x" Then
    Cells(Xcell.Row, 12).Interior.ColorIndex = 5
    Else
    Cells(Xcell.Row, 11).Interior.ColorIndex = 0
    End If
    Next Xcell[/VBA]
    Last edited by Aussiebear; 07-07-2011 at 09:11 PM. Reason: Applied VBA tags to code

Posting Permissions

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