Rank then color code

[ccmballroom]

I have a two part question

1) My data is in two columns. The first column is a sum from other worksheets. However, is there an easy way to rank but leave out certain rows. For example: (I am ranking Highest to lowest and giving every one a unique rank number even if duplicate)

Score - Rank
47 - 4
42 - 5
50 - 3
408
60 - 2
100 - 1
42 - 6

And I would not like to rank 408. I accomplished this by taking the scores to another table and just combining them without the rows I didn't need. Then under the Rank column I just attached it to this new table that rank the score. My question then is are there any ways to do this ranking without going to another table?

2) I am very new at macros but I would like a macro to color the interior red for ranks 1-5(just the box with the rank) and ranks 6-10 yellow. Now I would like this macro to be activated when the score changes (any score in the column). My problem has been that the scores are updated from other worksheets and when I change the value on other sheets my macro on the current sheet doesn't see the change.

Thank you,

[ccmballroom]

I solved the color coding by conditional formating. But, is there a better solution for ranking?