Creating a loop from my code
 

Below is the code that Nigel helped me with. Is there anyway to make this a
loop instead of this long code?

Thanx

Todd


If Application.CountIf(Range("IV1:IV100"), Range("V4")) 0 Then
Range("area22").Interior.ColorIndex =
Range("IV1").Interior.ColorIndex
Else
Range("area22").Interior.ColorIndex = 2
End If

If Application.CountIf(Range("IV1:IV100"), Range("W4")) 0 Then
Range("area23").Interior.ColorIndex =
Range("IV1").Interior.ColorIndex
Else
Range("area23").Interior.ColorIndex = 2
End If

If Application.CountIf(Range("IV1:IV100"), Range("X4")) 0 Then
Range("area24").Interior.ColorIndex =
Range("IV1").Interior.ColorIndex
Else
Range("area24").Interior.ColorIndex = 2
End If

If Application.CountIf(Range("IV1:IV100"), Range("Y4")) 0 Then
Range("area25").Interior.ColorIndex =
Range("IV1").Interior.ColorIndex
Else
Range("area25").Interior.ColorIndex = 2
End If

If Application.CountIf(Range("IV1:IV100"), Range("Z4")) 0 Then
Range("area26").Interior.ColorIndex =
Range("IV1").Interior.ColorIndex
Else
Range("area26").Interior.ColorIndex = 2
End If

Creating a loop from my code
 

V-Z is 22-26

For Index = 22 to 26

If Application.CountIf(Range("IV1:IV100"), _
Cells(4,Index)) 0 Then

Range("area" & index)").Interior.ColorIndex = _
Range("IV1").Interior.ColorIndex

Else

Range("area" & index)").Interior.ColorIndex = 2

End If

Next

Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Below is the code that Nigel helped me with. Is there
anyway to make this a
loop instead of this long code?

Thanx

Todd


If Application.CountIf(Range("IV1:IV100"), Range("V4"))
0 Then
Range("area22").Interior.ColorIndex =
Range("IV1").Interior.ColorIndex
Else
Range("area22").Interior.ColorIndex = 2
End If

If Application.CountIf(Range("IV1:IV100"), Range
("W4")) 0 Then
Range("area23").Interior.ColorIndex =
Range("IV1").Interior.ColorIndex
Else
Range("area23").Interior.ColorIndex = 2
End If

If Application.CountIf(Range("IV1:IV100"), Range
("X4")) 0 Then
Range("area24").Interior.ColorIndex =
Range("IV1").Interior.ColorIndex
Else
Range("area24").Interior.ColorIndex = 2
End If

If Application.CountIf(Range("IV1:IV100"), Range
("Y4")) 0 Then
Range("area25").Interior.ColorIndex =
Range("IV1").Interior.ColorIndex
Else
Range("area25").Interior.ColorIndex = 2
End If

If Application.CountIf(Range("IV1:IV100"), Range
("Z4")) 0 Then
Range("area26").Interior.ColorIndex =
Range("IV1").Interior.ColorIndex
Else
Range("area26").Interior.ColorIndex = 2
End If


.

Creating a loop from my code
 

Todd Huttenstine(Remote) wrote:
Below is the code that Nigel helped me with. Is there anyway to make this a
loop instead of this long code?

Dim I As Integer
For I=22 To 26

If Application.CountIf(Range("IV1:IV100"), Cells(4, I)) 0 Then
Range("area" & I).Interior.ColorIndex =
Range("IV1").Interior.ColorIndex
Else
Range("area" & I).Interior.ColorIndex = 2
End If
Next I


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

Creating a loop from my code
 

thanx to both. It worked.


-----Original Message-----
Below is the code that Nigel helped me with. Is there
anyway to make this a
loop instead of this long code?

Thanx

Todd


If Application.CountIf(Range("IV1:IV100"), Range("V4"))
0 Then
Range("area22").Interior.ColorIndex =
Range("IV1").Interior.ColorIndex
Else
Range("area22").Interior.ColorIndex = 2
End If

If Application.CountIf(Range("IV1:IV100"), Range("W4"))
0 Then
Range("area23").Interior.ColorIndex =
Range("IV1").Interior.ColorIndex
Else
Range("area23").Interior.ColorIndex = 2
End If

If Application.CountIf(Range("IV1:IV100"), Range("X4"))
0 Then
Range("area24").Interior.ColorIndex =
Range("IV1").Interior.ColorIndex
Else
Range("area24").Interior.ColorIndex = 2
End If

If Application.CountIf(Range("IV1:IV100"), Range("Y4"))
0 Then
Range("area25").Interior.ColorIndex =
Range("IV1").Interior.ColorIndex
Else
Range("area25").Interior.ColorIndex = 2
End If

If Application.CountIf(Range("IV1:IV100"), Range("Z4"))
0 Then
Range("area26").Interior.ColorIndex =
Range("IV1").Interior.ColorIndex
Else
Range("area26").Interior.ColorIndex = 2
End If


.