Creating a loop from my code

  • Thread starter Todd Huttenstine\(Remote\)
  • 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
 
P

Patrick Molloy

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
 
B

Bill Manville

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Match Function(Code modification) 3
It works but I need better code... 7
Need help changing Range 3
2 codes in one sheet 5
How can I modify my code to offset the defined range and repeat theprocedure instead of duplicating 4
Two codes in one set. 1
Colorvalue of cell Z1 2
Loop a code thru several worksheets 2

Top