View Full Version : Solved:Looping a section of code
lucsar
07-06-2011, 09:18 AM
Hi all,
I must check some conditions on a row like that:
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
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
07-06-2011, 10:01 AM
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
something like this should work, i use something similar that i just tweaked to find empty cells in a range
Bob Phillips
07-06-2011, 10:01 AM
Maybe something like this
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
lucsar
07-07-2011, 06:31 AM
Thanks guys!
here "my" solution:
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.