Conditional Formatting - comparing numbers

[davidingilbert]

I attempting to develop a spreadsheet that will compare the first three numbers in one column with the first three numbers in another.

The project i am doing is related to zip codes and need to be able to compare.

For example -

Zip code in Milwaukee - "53217"
First three digits for that part of the country - "532 - midwest"

I would like to use conditional formatting, but not sure how to create the formula.

Thanks.

[etaf]

can you attach a sample spreadsheet

are you comparing say cell A1 with everything in column B for example

maybe something like

=AND(A1<>"",COUNTIF($B$1:$B$100,LEFT(A1,3))>0)

[davidingilbert]

Wayne - Per your request, I am attching a version of the spreadsheet. What I am looking for is to compare the first 3 digits with the "main zip code" to the first three digits of the "Regional Zip Code" just to make sure things align correctly.

Thanks.
dave

[etaf]

try
=AND(B5<>"",COUNTIF(E$5:E$22,LEFT(B5,3)&"*")>0)

see attached

for 2007 or 2010 excel version
Conditional Formatting

Highlight applicable range >>

B5:B100


Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:

=AND(B5<>"",COUNTIF(E$5:E$22,LEFT(B5,3)&"*")>0)

Format… [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

[davidingilbert]

Wayne - one more question. How do i do the formula if I want to make all things "False" fill up with color?

[etaf]

OK - so I would just add a NOT in front to change the TRUE to a FALSE and a FALSE to a TRUE so conditional formatting works

=NOT(AND(B5<>"",COUNTIF(E$5:E$22,LEFT(B5,3)&"*")>0))

possibly
=AND(B5="",COUNTIF(E$5:E$22,LEFT(B5,3)&"*")=0)
May Work- Not tested though

[davidingilbert]

Thanks Wayne!

[etaf]

your welcome

if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here