Find Matching values from single row

I have a "master"row that contains a fixed number of values 10. I also have about 60 rows of the same length that i need to search if there are matching values from this "master" row. I would like to highlight the matching values.

Is there a quick way of doing this using functions or VBA. i have thought of using a nested for-loop...

for (int i = 1 to 10 )
for (int j = 1 to 36)
for (int k= 1 to 10)
if master[i] = Rest[j][k]
Rest[j][k].background = yellow
end if
end if
end if

Im familiar with programming but having difficulty transferring this to Excel VBA

Any help and or Advice greatly appreciated!

Does it matter if the numbers are not in the same column?

You can use Conditional formatting.

So assuming the Master row is A1:J1 and the 60 other rows are A2:J61, then

Select A2:J61 and go to Format|Conditional formatting.

Choose Formula Is from drop down menu

Enter formula: =MATCH(A2,$A$1:$J$1,0)

Note the $ signs...

Then click Format and choose colour from pattern tab.

If the match must also be in the same column, then use the conditional formula:

=A2=A$1

I would have thought you could use Conditional Formatting for this.

Please post a sample workbook with some typical data.

Regards

I did show Conditional formatting solutions....

@NVBC: yes - you were writing while I was still thinking ... ;-)

Match only works with 1 row or 1 column ranges....

try instead:

=COUNTIF($A$1:$J$13,A14)>0

where A14 is first cell in rows to match up to the top 13 rows (not sure why you have A2 in your attempt? Are you checking for duplicates within A1:J13?)

Originally Posted by NBVC

Match only works with 1 row or 1 column ranges....

try instead:

=COUNTIF($A$1:$J$13,A14)>0

where A14 is first cell in rows to match up to the top 13 rows (not sure why you have A2 in your attempt? Are you checking for duplicates within A1:J13?)

Say no more!!..you"ve HIT THE NAIL ON HEAD....thats seems to work for me just hope u not my BOSS undercover

i know im pushing it but can i continously activate this with a button on a daily basis?

I'm not making sense of this revision. You have 3 differently coloured columns and each cell has the word criterea... what exactly is supposed to be checked for?

Originally Posted by NBVC

I'm not making sense of this revision. You have 3 differently coloured columns and each cell has the word criterea... what exactly is supposed to be checked for?

At the moment if i populate row A3:J3 with values then matching values in A20:J:20 are automatically formatted. What i want to do is before i format the values in A20:J20, i must first check the value in K20 if this value is =0 dont format and if =1 then format

before formating values in A24:J:24 first check value in K24..and so forth so in other words before formatting values in a row check the "key" whick is always the value in the K-column.

****ignore the colours in the attachment i this this way is simpler

I hope this sounds clearer

Originally Posted by simon123

At the moment if i populate row A3:J3 with values then matching values in A20:J:20 are automatically formatted.

Do you mean A1:J2? (ie top 2 rows)

What i want to do is before i format the values in A20:J20, i must first check the value in K20 if this value is =0 dont format and if =1 then format

Do you mean any row starting from Row 9 in the sample attachment?

And we are only looking to column K to determine criteria for formatting?

Alright, so assuming you are entering numbers in A3:J3 and have 1's and 0's entered in K9:K34,

Then select A9:J34 and invoke Conditional Formatting

Formula Is >> =AND($K9=1,COUNTIF($A$3:$J$3,A9))

Format with colour from Pattern tab.