# Conditional formatting - maybe?

#### bds

##### Board Regular
I have a worksheet in Excel 2002. That worksheet has many rows of data. In column A of that data are 9 digit numbers. Often, there are two or three rows with the same 9 digit number. I have sorted the data from lowest to highest, so when the numbers are the same, they are one after the other. However, the report is hard to read. I'd like to somehow make is so there is a distinction between rows where the numbers differ. So, if the first 2 numbers match each other, then those rows could be white - and then the next 3 match each other and those 3 would be gray - and then the next two match either other and they would be white, etc. Alternatively, I'd be happy to insert a blank row whenever a new number shows up in sequence. Is there a way to do this?

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### Jonmo1

##### MrExcel MVP
Well, I don't know about alternating the color, but you can definately highlight any that are dups with conditional formatting.

formula is

=COUNTIF(A:A,A1)>1

#### Gerald Higgins

##### Well-known Member
Assuming you've already sorted your data based on this 9 digit number, you could identify duplicates in conditional formating.

For example, assume your 9 digit numbers are in Col A, starting on row 2.

Set your default format for the entire range, for example black text on a white background.
Then, in conditional formating, set your first condition for cell A2 as
Formula is,
Code:
``=or(a2=a1,a2=a3)``
and format as required, eg red text on a yellow background.
Note this will only identify duplicates - it will treat a pair of duplicates the same as three or more duplicates - if you want to treat pairs differently from triplets, you'll need something else.

#### Gerald Higgins

##### Well-known Member
Note that although both of these will work, jonmo1's solution is better, because you don't need to pre-sort your data - it will identify duplicates wherever they are in the column.

#### bds

##### Board Regular
I don't think I've explained myself clearly - or I don't understand the responses correctly. I know there are duplicated. I want to color the first set of duplicated one color (say white background with black text) and the 2nd set of duplicated another color (say gray background with black text) and then the 3rd set of duplicates would be white background with black text and the 4th set would be gray backround with black text and so forth. That way, when I read the info on the sheet, I know to compare the first 2 lines, then compare the next 3 lines, etc.

#### Gerald Higgins

##### Well-known Member
You could do this with a helper column.

For example, still assuming your 9 digit number is in Col A, starting on row 2, and that you have already sorted your data.

In cell B2, insert a 1.

Put this formula in Col B starting on row 3-
Code:
``=if(a3=a2,+b2,+b2*-1)``
and copy down as required.
This will put either a 1 or a -1 against every entry. Every time the 9 digit number changes, the number in Col B will flip between 1 and -1, and all entries with the same 9 digit code will have the same entry in Col B.
Then, use Col B as the basis for your conditional formating - for example
Code:
``=b2=1``
and format as required

#### bds

##### Board Regular
clever idea. Thank you. I am constantly amazed at how many talented people are willing to help folks with this program. I appreciate it. [/code]

Replies
2
Views
217
Replies
29
Views
659
Replies
1
Views
863
Replies
1
Views
453
Replies
2
Views
749

1,190,616
Messages
5,981,951
Members
439,745
Latest member
VBANewbieJohn

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back