Colors in Vlookup Tables

[saunderd]

I have an Excel 2002 spread sheet of prospective customer addresses t
include zip codes. I have a vlookup table containing my store number
and their zip codes. The store numbers are color coded to identif
specific store criteria. When I do a vlookup to match stores wit
prospective customers by zip code, the store numbers come in fine, bu
not in colors. How do I get the store numbers with their respectiv
colors to come through on my spread sheet

 

[Frank Kabel]

Hi
not possible with formulas. Formulas can only return
values but not formats. So no chance to achieve this

 

[Earl Kiosterud]

Saunderd,

Here's my blurb on using color as data. Hope it's helpful.

Using Formatting as data:

There's been a lot of the use of formatting, such as color, to store
information lately, then doing stuff like sorting based on color. This
isn't really the way Excel was designed. Note that by formatting, I mean
cell formatting, such as color, font, borders, etc., not sheet layout, as
the term formatting is sometimes also used. Formatting is really "output
only" -- not intended to be inputted by the user, then tested as data. It's
a better idea to put the information (a code or something) that the
formatting is being used to indicate into a cell in some way. Then use that
information to cause formatting to change, such as with Conditional
Formatting, or a worksheet_Change sub. Now the cell can be easily tested,
instead of testing the formatting. Formatting should not be manually
applied to a cell to store any kind of information. My 2¢.

 

[David McRitchie]

You could store the colorindex next to the store number to make
sure things are correct you could create a macro and run that
to update your table, but the main purpose would be to pull
in the number with VLOOKUP and then run the macro to
update your colors in the adjacent or other cell. That would be
the easiest on you system. You could go to Chip Pearson's
page http://www.cpearson.com/excel/colors.htm
and use the functions to determine the color, but I would be
very much surprised if they didn't cause your system to slow down,
because you would be running them every time you do a calculate
event.