Excel 2003 - copy target cell's pattern color in Cond Formatting 2

[Divercem]

I have a calendar for keeping track of employee time. I have several categories for not working on a weekday - Holiday, Plant Closed, Vacation, 1/2 day vacation, etc. Each of these categories need to be color coded, but conditional formatting only allows 3 conditions.

Currently, I have the 3 conditions written similar to the following:

Conditional Format 1: =E3=VLOOKUP(E3,Vacation,1) [COLOR=yellow]Yellow[/color]
Conditional Format 2: =E3=VLOOKUP(E3,Holiday,1) [COLOR=orange]Orange[/color]
Conditional Format 3: =E3=VLOOKUP(E3,xxxx,1) [COLOR=red]Red[/color]

Vacation, Holiday and xxxx are named subsets of the categories array.

I have the color pattern set in each cell of the categories array to what it should be on the timesheet. Is it possible to copy the color pattern from the target cell in the categories array?

And if that is possible can I utilize vlookup (or something similiar) to only have 1 condition that will check the cell value against the array and copy the matching color pattern?

Thanks

 

[SkipVought]

Hi,

You can use the Format Painter to copy formats.

"And if that is possible can I utilize vlookup (or something similiar) to only have 1 condition that will check the cell value against the array and copy the matching color pattern?"

I don't understand your question.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[Divercem]

Skip,

Thanks for the info on Format Painter. But I meant to ask if I could automate the process in conditional format 1 of cell C2 (for example). So that I could have 1 conditional format statement that would match the value of cell C2 (HOL) from an array (e.g. X1:X20) and apply the color of the matching cell in the array (HOL in cell X2) to cell C2.

Below is the month of Jan, 2008 with xxxx, Hol, and Vac. I want the cells with xxxx to be red, Hol to be orange, and Vac to be yellow. I am currently doing this with 3 separate conditional format statements, but I have more than 3 that need to be included, such as Sick, Plant Closed, Personal, Bereavement, etc.

Row  A      B      C      D      E      F      G
1   Sun    Mon    Tue    Wed    Thu    Fri    Sat
2   [COLOR=white red]xxxx[/color]   [COLOR=white red]xxxx[/color]   [COLOR=black orange]HOL[/color]    [COLOR=black yellow]VAC[/color]    [COLOR=black yellow]VAC[/color]    [COLOR=black yellow]VAC[/color]
3
4
5
6                                      [COLOR=white red]xxxx[/color]   [COLOR=white red]xxxx[/color]
7   [COLOR=white red]xxxx[/color]   [COLOR=white red]xxxx[/color]   [COLOR=white red]xxxx[/color]   [COLOR=white red]xxxx[/color]   [COLOR=white red]xxxx[/color]   [COLOR=white red]xxxx[/color]   [COLOR=white red]xxxx[/color]

and array X1:X20 (shortened)

Row   X
1  [COLOR=black gray]Closed[/color]
2  [COLOR=black orange]HOL[/color]
3  [COLOR=black yellow]V4[/color]
4  [COLOR=black yellow]V8[/color]
5  [COLOR=black yellow]Vac[/color]
6  [COLOR=white red]xxxx[/color]

I hope that clears up my original question...sorry I'm not more eloquent with the description.

 

[xlbo]

See this thread for a whole bunch of examples: thread68-223068

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Divercem]

Exellent examples Geoff,

I kept searching for "excel 2003 conditional format" and didn't see the thread you listed. I'll try to be a little more diligent in my searches.

Thanks for the info!

Thanks Skip for your info, too!

 

[SkipVought]

"I kept searching for "excel 2003 conditional format" and didn't see the thread you listed. "

It's not in Excel. Just CLICK the link in Geoff's post.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[Divercem]

I was just trying to explain that I did search for threads on conditional formatting before I posted this thread. And I didn't find the thread Geoff listed because I was too restrictive with the keywords I chose - "excel 2003 conditional formatting".

So, I guess I need to be more general on the searches and not restrict it with so many keywords.

You have helped me with several questions that I have posted so thanks for taking the time to make sure I found the thread.

Charlie

 

[xlbo]

No probs - wouldn;'t have found it with the 2003 keyword in there. To be honest, I have archived that thread as I could never remember what to search for !!!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question