multiple values in an array

[PeterJohns]

I'm having a thick moment!!
Basically, i have two columns of numbers. Column a is unique and column J has multiples of column a (though not all column a values match). I need to match and colour each corresponding value. The attached spreadsheet and macro works but i'm not sure how to increase the array so it looks for each value. Some of the spreadsheets i have to check are in excess of 100K rows. Ouch!

[AB33]

This will search in column A

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Vikas_Gautam]

hi,

I have solved your problem with a different code..
a subroutine named test()

before running the code, just Uncolor All the cells...
just run the code a enjoy the magic...

Vikas Gautam

Please Login or Register  to view this content.

[:) Sixthsense :)]

A simple countif() with Conditional Formatting (CF) method will do that

=COUNTIF($A$2:$A$113,B2)

B2 Should be the active cell while applying the CF

[Vikas_Gautam]

Does it help.... Peterjohns..

Just let me.. know...

Vikas gautam

Please Login or Register  to view this content.

[PeterJohns]

Vikas,
yes it does work. many thanks.

however, in Col D this is a hierarchal value, can be 10, 20, 30, 40, 50 or 100.
Would it be easy to modify your solution so that it only works on those with a value of a 100?

My fault I forgot that rather relevant point.

[PeterJohns]

AB33,
I have 2 problems with this. For some reason the 'Rng.Interior.ColorIndex = myColor(I)' throws up an error of 'Runtime 9 'out of range'
the second part is my own fault. Can it be modified so that that only values of 100 in Col D are coloured, i had forgotten about that when I send in the post.

many thanks in anticipation.

PeterJ

[AB33]

Okay! I can correct that error. As it is now, your code colours columns A and J.
"Can it be modified so that that only values of 100 in Col D are coloured?"
Do you mean if the rows in column J and A match and if the value if column D is 100, colour that cell?
There is no row where the match is found and column D value is 100. Or am I missing something?

[kalak]

Column a is unique and column J has multiples of column a (though not all column a values match). I need to match and colour each corresponding value.
...
Some of the spreadsheets i have to check are in excess of 100K rows. Ouch

Peter,

If this quote does indeed accurately describe your dataset, and you do have 100K+ rows, and want to bypass rows with 100 in col D, then you may like to try the following

Please Login or Register  to view this content.