Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing neat code
Can anyone tell me if there is a more elegant way of writing the code
below, please? Some explanation: rng1 is a large list of telephone numbers people have dialled. rng3, rng4, etc. are different ranges of telephone numbers belonging to certain departments. The object of the exercise is to see whether any of the departmental phones match any on the list in rng1. If they do, then turn the interior of the cell a particular colour depending on the department called. I must say that it all works well as it stands. It's just that I felt that there must be a neater way of writing the code. Thanks Roderick For Each cell In rng1 res = Application.Match(cell, rng3, 0) If Not IsError(res) Then cell.Interior.ColorIndex = 8 End If Next For Each cell In rng1 res = Application.Match(cell, rng4, 0) If Not IsError(res) Then cell.Interior.ColorIndex = 10 End If Next For Each cell In rng1 res = Application.Match(cell, rng5, 0) If Not IsError(res) Then cell.Interior.ColorIndex = 12 End If Next For Each cell In rng1 res = Application.Match(cell, rng6, 0) If Not IsError(res) Then cell.Interior.ColorIndex = 14 End If Next |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing neat code
For Each cell In rng1 res = Application.Match(cell, rng3, 0) If Not IsError(res) Then cell.Interior.ColorIndex = 8 res = Application.Match(cell, rng4, 0) If Not IsError(res) Then cell.Interior.ColorIndex = 10 res = Application.Match(cell, rng5, 0) If Not IsError(res) Then cell.Interior.ColorIndex = 12 res = Application.Match(cell, rng6, 0) If Not IsError(res) Then cell.Interior.ColorIndex = 14 Next This will execute faster, also. -- - K Dales "Roderick O'Regan" wrote: Can anyone tell me if there is a more elegant way of writing the code below, please? Some explanation: rng1 is a large list of telephone numbers people have dialled. rng3, rng4, etc. are different ranges of telephone numbers belonging to certain departments. The object of the exercise is to see whether any of the departmental phones match any on the list in rng1. If they do, then turn the interior of the cell a particular colour depending on the department called. I must say that it all works well as it stands. It's just that I felt that there must be a neater way of writing the code. Thanks Roderick For Each cell In rng1 res = Application.Match(cell, rng3, 0) If Not IsError(res) Then cell.Interior.ColorIndex = 8 End If Next For Each cell In rng1 res = Application.Match(cell, rng4, 0) If Not IsError(res) Then cell.Interior.ColorIndex = 10 End If Next For Each cell In rng1 res = Application.Match(cell, rng5, 0) If Not IsError(res) Then cell.Interior.ColorIndex = 12 End If Next For Each cell In rng1 res = Application.Match(cell, rng6, 0) If Not IsError(res) Then cell.Interior.ColorIndex = 14 End If Next |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing neat code
Thanks a lot.
It works a treat! And looks neater too. Code like what it should be proper writted! Roderick On Fri, 30 Dec 2005 10:47:02 -0800, "K Dales" wrote: For Each cell In rng1 res = Application.Match(cell, rng3, 0) If Not IsError(res) Then cell.Interior.ColorIndex = 8 res = Application.Match(cell, rng4, 0) If Not IsError(res) Then cell.Interior.ColorIndex = 10 res = Application.Match(cell, rng5, 0) If Not IsError(res) Then cell.Interior.ColorIndex = 12 res = Application.Match(cell, rng6, 0) If Not IsError(res) Then cell.Interior.ColorIndex = 14 Next This will execute faster, also. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Neat Trick | Excel Discussion (Misc queries) | |||
Need help writing Code | Excel Discussion (Misc queries) | |||
Sharing a neat trick | Excel Discussion (Misc queries) | |||
Need some help writing code. | Excel Programming | |||
Keeping macros neat | Excel Programming |