Horizontally challenged...

[Trixie]

Hi all,

Why can the boss never work in a vertical world?

-"Can you copy, paste & transpose each row in these two worksheets an
then highlight the changes for me?" the boss asks...-

Sheet1 is my baseline, containing approximately 1582 rows of data; 21
columns. Sheet2 is a newer version, 2 months later.

-"I will need you to do this every two months" the boss goes on
ignoring the evil eye i directed her way.-

I've said my prayers, rubbed my magic eight ball, and am checking wit
the experts here to see if this can be done with VBA, looping throug
the rows and comparing data to see where updates have occurred, hopin
that I don't end up in copy/paste/cond-form purgatory...

In order for each row to be considered for compare between th
worksheets, the values in the A, B, C, D & H cells will need to be a
exact match (they will be unique values and will occur within bot
worksheets).

Once a row from the baseline identifies a match row, I would like t
have the cell background formatted in pink.

Example, row 2 from Sheet2 "matches" row 1321 of Sheet1 (based on th
above matching logic), and the data in AB2, AD2, BR2, CI2 & CX2 ha
changed on Sheet2 from the data as found in Sheet1.

Thank you for your time

 

[joel]

You can use a conditional format in sheet 2 to match sheet 1

Put this into the conditional formating in sheet2!A2
=Sheet1!A1321=A2

Now once you setup the conditional formating in sheet 2 you can copy
the cells from sheet 1 without destroying the conditional formating by
copy and pastespecial using both values, and Transpose

Sheets("Sheet1").Range("A2:G7").Copy
Sheets("Sheet2").Range("A2").PasteSpecial _
Paste:=xlPasteFormats, _
Transpose:=True

 

[Trixie]

Will this also work if it the match is any other row between the new
version to the baseline?

The rows I indicated were just an example....row 35 could actually be
the match for row 1321...

Thanks~

You can use a conditional format in sheet 2 to match sheet 1

Put this into the conditional formating in sheet2!A2
=Sheet1!A1321=A2

Now once you setup the conditional formating in sheet 2 you can copy
the cells from sheet 1 without destroying the conditional formating by
copy and pastespecial using both values, and Transpose

Sheets("Sheet1").Range("A2:G7").Copy
Sheets("Sheet2").Range("A2").PasteSpecial _
Paste:=xlPasteFormats, _
Transpose:=True

 

[joel]

Your original instructions inplied that the there was the same amount o
data in the previous weeks and new week and the old and new table was i
the same Ranges. If so you can put the conditionl formating in th
first cell on sheet 2. Then copy the conditional formating to the othe
cells by copy the cell with the conditional formating, select all th
cells on sheet 2 that need to be compared, then use PasteSpecial usin
FORMAT only to paste the conditional formating to all the cells.

If the sizes of the data and the locations change each week then I nee
more information. I need some indication where the compare data (bot
sheet 1 & 2) is located and the number of rows and columns to copy

 

[Simon Lloyd]

Trixie, to further aid your question and Joe
*Why not add a workbook?*
Providing a workbook will not only get you your answer quicker but wil
better illustrate your problem, usually when we can see your data (-i
can be dummy data but must be of the same type-) and your structure i
is far easier for us to give you a tailored, workable answer to you
query :

Attachments.

To upload a workbook, click reply then add your few words, scroll dow
past the submit button and you will see the Manage Attachments button
this is where you get to add files for upload, if you have any troubl
please use this link or the one at the bottom of th
any page

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[Barb Reinhardt]

FWIW, some interfaces don't allow for workbooks to be uploaded.

 

[Rick Rothstein]

And to follow up Barb's message...

Workbooks can be posted on the Internet for downloading by other. Here are
some free posting websites where you can do this from...

http://www.mediafire.com/
http://www.freefilehosting.net/
http://savefile.com/