Writing neat code

[Roderick O'Regan]

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

[K Dales]

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
>

[Roderick O'Regan]

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"
<[email protected]> 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.