Comparing data.

**judygodchild218** · 06-05-2008, 08:16 AM

I have a range of data in cells E5 to H51. I would like to run a macro to compare the data all of these cells and highlight the cells which have the same data. Is there a way I can do this??? The End of the data range changes everytime new data is added to it..

**jasoncw** · 06-05-2008, 08:20 AM

Rather than running a macro, what about conditional formatting?

Starting with cell E5, select E5:H51.

Format > Conditional Formatting

Select Formula Is from the drop-down, and type:

=COUNTIF($E$5:$H$51,E5)>1

Then select the cell pattern color you wish.

HTH

Jason

Edit: Whoops, I just saw that the data changes all the time. Another option is rather than using $E$5:$H$51, use a dynamic named range, then use that name in the formula. For example:

=COUNTIF(MyRange,E5)>1

Here is a description of dynamic named ranges:

http://www.ozgrid.com/Excel/DynamicRanges.htm
http://www.cpearson.com/excel/named.htm
http://www.contextures.com/xlNames01.html

HTH

Jason

**judygodchild218** · 06-06-2008, 07:28 AM

Wow... That was simple... It works... Thanks so much...

**jasoncw** · 06-06-2008, 08:30 AM

Glad it helped.

Jason

**judygodchild218** · 07-22-2008, 05:56 AM

I have another question along the same lines. Is there any formula I can use in conditional formatting where all the cell which have data consisting of the same 3 to 4 characters at the beginning? Eg. A1 to A38 may have a few cells which could have "PPSS" as the first 4 characters followed 2 characters (which are variable). Is there any way the cells containing PPSS* (* for the remaining 2 variable characters) can be highlighted?

**jasoncw** · 07-24-2008, 02:23 PM

Hi, Judy.

If you only are looking for cells where the left 4 characters are "PPSS", you could use:

=LEFT(A1,4)="PPSS"

Or if you need the above, and it needs to be 6 characters in length, you could use:

=AND(LEFT(A1,4)="PPSS",LEN(A1)=6)

HTH

Jason