How do I associate 2 values needed for 1 cell

[projecttoday]

I am creating an Excel spreadsheet with vba code which is executed in an Access database. Currently I am using transferspreadsheet.

There are text values that go into the cells of the spreadsheet. Each value has a number associated with it that I want to use to set the color of the cell but which does not appear in the spreadsheet. How can I associate a
formatting with a cell?

Example:

Value/code

ABCDE/1
FGHIJK/2
LMNOP/1
QRSTU/1
VWXYZ/3

codes
1 - red
2 - blue
3 - green

In the spreadsheet, in the cell with ABCDE, the color of the cell should be
red. For FGHIJK, the color of the cell is blue. Etc..

I need to put the value and its associated color in the spreadsheet. The best way I've come up with is to use a recordset or an array in the Access program that would mirror or map into the spreadsheet that I have already created with the transferspreadsheet. But I was wondering if there was a less awkward way, like a tag value or something?

Robert

 

[SkipVought]

Hi,

Use Format > Conditional Formatting... which is an Excel feature.

Once you figure out how that works, you can turn on your macro recorder and generate code for the CF.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[projecttoday]

Conditional formatting will not help without the condition. How can I pass the condition to the spreadsheet?

 

[SkipVought]

You condition is in your cell value. Happens to be the RIGHT 1 byte.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[projecttoday]

My example wasn't very good. There are 2 separate values, the contents of the cell and the code for the color. So if I pass the actual color value, you could have something like:

Value/color
ABCDE/123456
FG/2345678
LMNO/14291
QRS/19121
VWXYZABCDE/332

Right now I'm only passing the value. Now that you mention it, / is never part of either, so I could pass the conbined value and split on the /. The best idea so far. I was just wondering if there was a tag or anybody had any other ideas.

 

[SkipVought]

I am TOTALLY confused by your lack of consistancy.

You specified THREE colors.

In your NEW example you have FIVE different values after the SLASH.

How about you start again and tell us what your REAL requirements are. Please be very specific.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[projecttoday]

All I'm trying to do is create in Access a spreadsheet of values which have their own color formating values that go along with them. The color formating values, which can be any number, must not actually appear in the cell in the finished spreadsheet. Since a SELECT statement can't say "this is a value" and "this is a format code", I think that concatening them together with a slash in-between is the best I'm going to do.

Now what I need is the automation code so that I can loop through the cells and color them from the part to the right of the slash and then delete that part.

 

[SkipVought]

OK, in Access you are entering some values, followed by a slash and a number that represents a color.

So what's the LOGIC for entering this number?

Is this all MANUAL?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[projecttoday]

The user selected the number previously.

This is for automating the creation of the spreadsheet.

 

[SkipVought]

The user selected the number previously.

Selected the number, based on WHAT?

Day of the week?

How they felt?

What they had for breakfast?

Nuber of hours of sleep?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[projecttoday]

Based on how they felt / their choice.

 

[SkipVought]

What's the business case for this requirement?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}