Search and mark specific values in cells

Hi all

I've got an Excel-File with lots of different sheets (for each month one, named as: Jan, Feb, Mar, ... Dec.). Each Sheet has the same Layout. In each row (from row 5 downwards) is a "report". Each "report" can have up to 200 actions. A "report" is split up in the following columns (please see the attached "Demo-File.xls" for an example; there are only 3 three sheets, "Jan", "Feb" and "Mar"):

• Column A = Amount of Actions (all actions (from columns E to IV) are counted here, e.g. if the "report" in row 5 has 3 actions, there would the number 3 in cell A5)
• Column B = ID (ID for the "report", e.g. AA or BB or XY, etc.)
• Column C = Year (the year of the "report", e.g. 2009)
• Column D = No. (the number of the "report", e.g. 1, 2, 999, etc.)

This will never change. The "Amount of Actions" will always be in a cell in column A, the "ID" always in a cell in column B, "Year" always in a cell in column C and "No." always in a cell in column D.

• Column E to IV = Actions (one action per cell; if the "report" in row 5 has 3 actions, the first action will be in cell E5, the second action in cell F5 and the third action in cell G5)

The order of "Actions" can vary.

Imagine that on each sheet (Jan, Feb, Mar, Apr, ... Dec), there are hundreds of "reports" with dozens of actions each. Each sheet has the "reports" of the previous month in his rows, e.g. if on sheet "Jan" there are the reports 1 (in row 5), 2 (in row 6), 3 (in row 7), 4 (in row 8) and 5 (in row 9), on the sheet "Feb", these 5 "reports" should be there too, but not necessarily in the same rows (that's one of the problems). And it could also be, that the actions aren't in the same order, e.g. if the "report" 1 on row 5 on sheet "Jan" has the three actions "1a" in cell E5, "2b" in cell F5 and "3c" in cell G5, it could be that the "report" 1 on sheet "Feb" is now in row 100 and the three actions are in three different cells, e.g. the action "1a" is now in cell F5, the action "2b" in cell G5 and the action "3c" in cell E5.

Now it would be nice, if the cells, which have identical "values" (where the values do match) are marked with green colour and all those which haven't an equivalent would be marked with red colour.

The above mentioned example: "report" 1 on sheet "Jan", row 5 with 3 the actions "1a" (cell E5), "2b" (cell F5) and "2c" (cell G5) should be marked green, even though its "equivalent" on sheet "Feb" is in row 100 and the actions are in different cells, BUT, and that is important, it has the same values (not in the same row and same order, but the same values).
If (in that example) one of the three actions (on sheet "Jan") has a different value on sheet "Feb", e.g. action "1a" is still on sheet "Feb" and also action "2b", but action "3c" (on sheet "Jan") is on sheet "Feb" now "3z" => this cell should be marked red (all the other cells which match => green).

Now, is it possible to write a macro (or is it even possible with formulas?), which can "handle" this (automated)? Or has anyone an idea how I could deal with this "issue"?


I hope you understood what I've written. English is not my native language.

Best regards

RollWeb

Hi

I think there is a misunderstanding. I'm searching for a method with which it is possible to compare rows in different sheets with each other. If all values in the cells in a row match the values in cells of another row on a different sheet, then all those matching cells should be marked with a green colour, else with red colour.

Is the description (and / or english) in my first post really so bad, that it is not possible to understand what I'm searching for (I don't want to offend you)?

Regards
RW

No ideas?