Convert State abbreviations color coded cell

E

exceluser

I would like to take fifty state abbreviations, divide into 4 times zones
each with each zone (with multiple states) having its own color. For example,
anytime Excel sees CA,NV,OR,WA it colors the cells red. If it sees TX, OK,
MO, AL, WI etc it colors the cells green. Yellow for east coast, etc. The
only way I could figure to do this was to convert the text to numbers then
use conditional formatting but didn't see a function that would work on state
abbreviations. Thank you
 
G

Gary''s Student

Normally you could create a lookup table with state abreviation in one column
and zone in the adjacent column. You caould then apply conditional
formatting based upon a simple VLOOKUP equation.

Problem is that some states are in two zones. States like:

Idaho
Kansas
Kentucky
etc.
 
J

JE McGimpsey

Hmmm... Given that there are 6 US time zones, that 14 states have more
than one time zone, that AZ doesn't change to DST, and that *parts* of
IN don't change, you may need to provide more info...

For a worksheet function/CF solution, create a table of states and
integers, then use VLOOKUP() in your CF.

Or you could use either a Worksheet_Change() or Worksheet_Calculate()
event macro
 
L

Luke M

To expand on Gary's reply, you conditional format would be

Formula is: =IF(VLOOKUP(A1,$G$1:$I$50,3,FALSE)=1,TRUE,FALSE)

Where A1 is the cell the format is appied to, and G1:I50 is your vloopup
table.

Also, conditional formats have a limit of 3. (annoying) You could get around
this by making the default background one of the colors you want. Then the
conditional format will change all the others.
 
E

exceluser

Thank you!

Gary''s Student said:
Normally you could create a lookup table with state abreviation in one column
and zone in the adjacent column. You caould then apply conditional
formatting based upon a simple VLOOKUP equation.

Problem is that some states are in two zones. States like:

Idaho
Kansas
Kentucky
etc.
Click to expand...
 
E

exceluser

Thank you!

Luke M said:
To expand on Gary's reply, you conditional format would be

Formula is: =IF(VLOOKUP(A1,$G$1:$I$50,3,FALSE)=1,TRUE,FALSE)

Where A1 is the cell the format is appied to, and G1:I50 is your vloopup
table.

Also, conditional formats have a limit of 3. (annoying) You could get around
this by making the default background one of the colors you want. Then the
conditional format will change all the others.
Click to expand...
 
E

exceluser

THanks!

JE McGimpsey said:
Hmmm... Given that there are 6 US time zones, that 14 states have more
than one time zone, that AZ doesn't change to DST, and that *parts* of
IN don't change, you may need to provide more info...

For a worksheet function/CF solution, create a table of states and
integers, then use VLOOKUP() in your CF.

Or you could use either a Worksheet_Change() or Worksheet_Calculate()
event macro
Click to expand...
 
E

exceluser

Hi Luke...i'm still trying to figure this out. If I make a lokup table
w/state abr in one collumn and zone in the adjacent what is in the third
column? I noticed that the formula example below contains rows g, h and I.
also, am i setting up my conditional format options based on the zone numbers
1-4 or on a range of state names contained within a zone? wasn't sure by what
you meant when you said A1 is the cell the format is applied to. Is A1 the
first state name of the fifty states that have yet to be color coded? Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

converting a for next loop into a for each loop 2
Text vs. Numbers in a data series 0
Return US State Name or Territory from USPS Abbrevation - an example 3
Function to cnvrt Phone to State w/out cell ref 2
HELP W/ VBA: SELECT RANGE, ALLCAPS, CELL COLOR, RETURN TO BLANK CELL/PATTERN CELL 5
Statement on Spam and VoteNader.org 0

Top