Creating a loop from my code

  • Thread starter Thread starter Todd Huttenstine\(Remote\)
  • Start date Start date
T

Todd Huttenstine\(Remote\)

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
 
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
 
Todd said:
Below is the code that Nigel helped me with. Is there anyway to make this a
loop instead of this long code?
Click to expand...

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
 
Back
Top