Conditional Formatting based upon the color of a cell

[input1000]

I have a matrix which is automatically create.
100x100 cells large.

The matrix is filled with values 1-a to 1-z depending upon earlier calculations.

1-a through 1-z are sub-parts to a larger groups (there gates 1 - 8)

Example... Gate 1 includes (1-a, 1-b, 1-c .... 1-f)

I have another sheet that has the matrix values with there relations ship to the gate.

What I require is some way to set the conditional formatting so that if I change the color for gate 1 it changes everything in the matrix.

 

[huggyboy]

Might help if you posted the workbook you have done already.
If it were my problem I would be writing some VBA to do it - because you have a relationship between the gate and the matrix via the relationship between the gate and the values - probably not something you can do via conditional formatting (i wish i hadnt said that)

 

[SkipVought]

Hi,

Conditional Formatting is based on VALUES.

If YOU change a COLOR, no VALUE has changed. So there's no criteria that has changed for the CF to change a format.

Please explain exactly what needs to change, based on YOU changing a color somewhere.

Please be Clear, Concise & Complete.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[input1000]

Would love to pasted the book I created but I'm having trouble with that cause I don't have an online place to store it. If someone wants to give me an address to send it to I would send it off.

As to Skip's question. I was hoping to make the conditional formatting(you know how you get to choose the color you want?) based upon a cell if possible. The rest I can do manually. It's just that we often change the colors and changing the conditional formatting everytime would be a pain.

 

[SkipVought]

I am asking for the LOGICAL relationship between the CELL VALUE containing the color that you change and the criteria for the CELLS that you want affected by that change.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

input1000,

Basically, so long as you aren't changing cell colors on a whim or just b/c you feel like it, then there's some logical reason for doing so.

That REASON can be known/calculated. And if it CAN be known or calculated, then it can be placed in a cell as a value. That VALUE can drive the conditional formatting.

At least I believe that's what SKIP is trying to get you to see.

As far as uploading a file, here's a place that should work:

http://skydrive.live.com/

There are others as well, but that should be MORE than what you could possibly need there.

If you can provide more information, and the outcome is at all possible, then you'll be able to get an answer here... especially if folks like SkipVought are looking at the problem.

--

"If to err is human, then I must be some kind of human!" -Me

 

[input1000]

Thanks

This should help you guys understand what I'm doing.

http://cid-89754df45f8ee6d2.skydrive.live.com/self.aspx/.Documents/example.xls

 

[SkipVought]

Clear as mud!

SHOW what you want to happen by a progressive example.

First the user does THIS with a color.

Second the response will be THIS.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

OK. Here's a solution.

1. Make a list of gates called GateList. My GateList is
[tt]
GateList
1
2
3
[/tt]
Use GateList in a Data > Validation -- LIST

Name the cell containing the Data Validation, SelectedGate

Name the ranges for System & Gate, using the headings in the TOP ROW.

Set 3 Conditional formats, of for each color...
[tt]
=INDEX(Gate,MATCH(A1,System,0),1)=IF(SelectedGate=1,SelectedGate,0)

=INDEX(Gate,MATCH(A1,System,0),1)=IF(SelectedGate=2,SelectedGate,0)

=INDEX(Gate,MATCH(A1,System,0),1)=IF(SelectedGate=3,SelectedGate,0)
[/tt]

Select a Gate in the Data Validation Drop Down Box and watch the colors change in the array. NOTE that the colors change as a result of a VALUE changing, not a COLOR.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

hello input1000.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[input1000]

I'm trying to make that work. haha. slowly.

I attached a progressive example of what I'm trying to do. Maybe this will help you better understand.

Sorry you have to bare with me, I'm not so good with excell. I'm better with access.

 

[SkipVought]

What you want to do cannot be done with native Excel conditional formatting.

You will need to code a solution in VBA.

Have you ever used macros before?

Post in forum707, if you would like some help, but you'll need to do some probramming.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[input1000]

Thanks for the help skip!

 

[huggyboy]

Are you still interested in solving this problem via the programming route?

 

[input1000]

For sure if you have an idea. I have been taking my time with this one. It's not a priority right now, but any help would be appreciated.

 

[huggyboy]

As Skip said - post it on the VBA forum and let the clever people there have a look at it - if you havent done any programming it would be a start recording a macro and looking at the vba it produces - record changing the background colour of a cell - thats what the vba will have to do

 

[combo]

If your project will not expand (esp. more gates), probably pure excel will be sufficient. The steps:
- use conditional formatting following description by Skip,
- use (CF) three colours that will not be used in other cells in the spreadsheet,
- change colour palette to change those three colours (Tools>Options>Colours).

combo