Expert: Chad Welch - 4/6/2009

Question
Hi Chad,

I need some help from you. I really wish and hope you can help me on this. This is solve a very big problem of mine.

I have a excel file (Microsoft office 2007 version). I have so much of data in it.

My excel file looks some what similar to the below one...

  |     A        B        C         D        E
----------------------------------------------------
1  |    XYZ      ASD      23WF      323      SDF
2  |    XYZ      S2F      AD23A     323      DFS
3  |               
4  |
  
My job is finding common cell values like in A1 and A2, or B2 and B3, or D32 and D33... if they are common, then I should color the down cell as red (For example, if I’m checking A1 and A2, and if these two cells have same value then I should color A2 as red). Likewise, checking B1 and B2 and coloring them if they have same value... if not then no changing of color. and it continues...

What I’m looking for is, if i select two rows (For example, from A1:E2) and run macro, it should automatically color the below Row Cell as Red if they have common cell values as above cell.

I have loads of data so it is taking lots and lots of time for me to manually do it and there is possibility of the human errors also.

Pleaseeeeeeeeeeee help me with create a macro for this... i would be really grateful to you......

Thanks in Adv.

Regards,
Sirajuddin


Answer
Sirajuddin,
Good news. This will be a much easier project than you anticipate. It can be done with conditional formatting instead of a macro.

First, do you want to only color the cell if the duplicate data are in adjacent rows, or if the duplicate data is anywhere in the column (for example A1 is the same as A5), or if the duplicate data is anywhere in the table (for example A1 is the same as B2)? I'll show you the method for each scenario and you can pick the one that fits your needs.

To highlight any duplicate data in the entire table, highlight the table and then choose Conditional Formatting on the Home ribbon. Then click Highlight Cell Rules>Duplicate Values.

To highlight any duplicate data in the column, follow the same process as above, but only select one column at a time instead of the entire table.

To highlight duplicate data in adjacent rows, highlight the table from the second row to the bottom (A2:E20 for example). Then choose Conditional Formatting on the Home ribbon. Click manage rules and then add a new rule. In the dialog box that opens, choose "Use a formula to determine which cells to format." In the formula box type =A1=A2. Click the format button to select how you want to format the duplicate data and then close all open dialog boxes.

Let me know if you have any other questions or problems using conditional formatting.

Best regards,
Chad Welch