Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Creating a loop from my code
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default 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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 391
Default 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


.

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 473
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 237
Default 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


.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with creating a loop Woodi2 Setting up and Configuration of Excel 1 April 19th 09 09:10 PM
creating a loop JakeShipley2008 Excel Discussion (Misc queries) 2 September 9th 08 02:17 PM
Creating a loop JakeShipley2008 Excel Discussion (Misc queries) 1 September 8th 08 10:26 PM
Need help creating Loop Jenny B. Excel Discussion (Misc queries) 3 February 14th 07 11:26 PM
Loop for VBA code? paulinoluciano Excel Worksheet Functions 5 December 28th 05 01:30 PM


All times are GMT +1. The time now is 02:09 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement