Conditional formatting different number = different color going down column

[svendiamond]

I have a very simple conditional formatting problem. I have a table like this:

I want to have each new door number's row be highlighted, for example the first one 114 can be white, and the next one 115 gray, then 122 white, 123 gray, 129 white, 130 gray... etc etc...

AKA I want it to look like this:

There's got to be an easy way to do this, right?

 

[RonB1111]

Select A2:B1000 (if rows extend below 1000 adjust accordingly)
Click Home - Conditional Formatting - New Rule
In the window that pops up, click "Use a formula to determine which cells to format"
Enter this formula in the box below "Format values where this formula is true"

=IF($A2<>"",ISEVEN(SUM(IF(FREQUENCY($A$2:$A2,$A$2:$A2)>0,1))))

Click the "Format" button
On the Fill tab, select a color, then click OK, OK

This assumes only numbers (no text) in your col A data

 

[svendiamond]

Select A2:B1000 (if rows extend below 1000 adjust accordingly)
Click Home - Conditional Formatting - New Rule
In the window that pops up, click "Use a formula to determine which cells to format"
Enter this formula in the box below "Format values where this formula is true"

=IF($A2<>"",ISEVEN(SUM(IF(FREQUENCY($A$2:$A2,$A$2:$A2)>0,1))))

Click the "Format" button
On the Fill tab, select a color, then click OK, OK

This assumes only numbers (no text) in your col A data

Thanks for the response. I tried your solution and it highlights my entire table. I've done this before with door numbers in order like 114, 115, 116, 117, where I can do a simple =ISEVEN($A2) for the conditional formatting and it will only highlight the rows with even door numbers... but on this list, the door numbers jump from 115 to 118, then 127, or whichever ones are currently in use. I need to highlight every other row of different door numbers. That way it will be easier to see all the information for each door. Thanks for the help and any other solution you can provide.

 

[RonB1111]

It works properly for me. Are your Col A numbers formatted at text? Can you upload your file to a file sharing service so I could download it without a password?

 

[Martyinco]

I think the issue is a simple type in Rob's first response, "Select A2:B1000" should be A2:A1000"

 

[RonB1111]

I think the issue is a simple type in Rob's first response, "Select A2:B1000" should be A2:A1000"

Both Col A & B are included so that both are highlighted when the condition calls for it. Note the placement of $'s in front of the A so the formula will be the same for both Col A & B.

Svendiamond,
Also make sure any previous conditional formats you might have used have been cleared.

Ron

 

[svendiamond]

It works properly for me. Are your Col A numbers formatted at text? Can you upload your file to a file sharing service so I could download it without a password?

Ah, sorry. You were right. My numbers were formatted as text. The code works correctly. Thank you.

 

[RonB1111]

You're welcome!