Solved:Looping a section of code

[lucsar]

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

[CatDaddy]

[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

[Bob Phillips]

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]

[lucsar]

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]