Find cell with identical value and copy it's background color

[PvanS]

Dear all

I have two sets of data on a worksheet. One set of data is spread over the range A2:U14. The second set of data is written in the column X2:X40. 1 cell in the range has the identical text to 1 cell in the column.

I am trying to create a macro that, when run, performs the following task:

Finds the matching cell in the column X2:X40 and the cell in the Range A2:U14.

Changes the formatting of the cell background in X2:X40 to the same background color to its corresponding cell in A2:U40.

For example:

Cell A9 has "John" written in it and a yellow background
Cell X4 has "John" written in it and a clear background

During macro:
finds these two matching cells
changes X4 so that it also has a yellow background

After macro:
Cell A9 has "John" written in it and a yellow background
Cell X4 has "John" written in it and a yellow background

Have been searching for answer but am still completely nowehre. Please can someone help me with the code?

Thanks for your help

Kind regards,
Paul

[smuzoen]

When you post problems like this it makes it much easier if you could provide a sample workbook with no sensitive data as it makes it easier to write the code otherwise the person trying to answer your question may have to create their own workbook to test the code. There is a discrepency in your post because you say

Finds the matching cell in the column X2:X40 and the cell in the Range A2:U14.

then you go on to say

Changes the formatting of the cell background in X2:X40 to the same background color to its corresponding cell in A2:U40.

I am not sure if the range should be A2:U40 or A2:U14 so you will need to change the code accordingly to match your workbook.
Try this code - it should do what you need. Let me know if you have any problems - add the following code to a module and run matchVals
NOTE - Change A2:U14 to A2:U40 if latter is correct. Make sure you run this on backup as you cannot undo VBA code. Also change Sheet1 to the name of the sheet with the data.

Please Login or Register  to view this content.