Complex Conditional Formatting

I have colour code combinations which relate to numbers e.g.

0.5 = Red, Red
0.9 = Yellow, Black
1.7 = Brown, Black
2.25 = Orange, Black
3.5 = Green, Black
4.5 = Grey, Grey
6 = Red, Black
etc.....

There are approx 50 numbers each with a unique two colour combination from 10 colours available.

Assume that in cell A1 of the Excel spreadsheet I want to type in any one of the 50 different numbers. I need this then to conditionally format cell B1 pattern to the first colour, and cell C1 to the second colour. i.e.

Cell A1 = 0.9
Cell B1 to have Yellow background (no text in cell required)
Cell C1 to have Black background (no text in cell required)

In reality this will be repeated in rows 2, 3, 4, 5, etc. with different numbers.

The simple solution would be conditional formatting, however this only allows a maximum of 3 conditions as opposed to 50.

I also tried using vlookup to a table of the combinations in a separate sheet. This would return colour names ok but wouldn't copy formats. If there is a way of getting vlookup to return formats as well as cell contents, that would be great.

I don't mind having to run a macro to format the cells (my attempts at this have failed), but use of a function in the cell which could do the same task automatically would be even better.

Any ideas/solutions would be greatly appreciated!

What hapens if the value is not in the list, e.g., 0.6?

Suggest you make a 3-column named range: value, colorindex 1, colorindex2 (red = 3, black =1, yellow = 6, ...). The VBA to implement the formatting would be simple.

I attached a file that I does what you asked for (I think).

The file DOES contain VBA code....and instructions....and documentation.

Is that something you can work with?

Hi, Satchmo

Here's what you're missing when you try to modify the file I posted:
The model uses Range Names. A Range Name is a nickname for a range of cells. Range Names can be used in formulas and programming to make the formulas or code easier to read.

For instance, if Sheet25, Cell AB102 contains the effective tax rate for your company, you can name that cell Eff_Tx_Rate. Then a formula would look like this: =Eff_Tx_Rate instead of =Sheet25!$AB$102........and you'd know what it referred to!.

The list in cells I16:J25 is only there to show you the Range Names I used and which cells they refer to.

To easily select any Named Range:
Press the [F5] key........a shortcut for <edit><go to>
...Choose the Range Name you're interested in from the list.
...Click [OK]....The cells referenced by that Range Name will be selected.

Here's an example for your file:
Press the [F5] key........Select: Red_Color....Click [OK]
Cell F3 will be selected.
Now look in the Name Box (above the Col_A header)
....it indicates the Range Name: Red_Color.
If you move to F4, the Name Box indicates: Black_Color.
Those cells are NAMED.

To view, edit, or create Range Names:
From the Excel Main Menu: <Insert><Name><Define>
The top box is where you'd type a new name to be created.
The middle box is a list of existing Range Names
The bottom box shows the cells that are referenced by the selected Range Name.

If you choose LU_ColorSchemes,
you'll see that it refers to: =Sheet1!$A$3:$C$9

To change that reference to include Row_10,
just edit the Refers To box to: =Sheet1!$A$3:$C$10
Then Click [OK] if you're done....or Click [Add] to continue.

Does that help?
Post back if you have more questions.

Posting your actual file helped quite a bit.

I reposted it as version 1a with the following changes

Please Login or Register  to view this content.

Let me know if that helps.

Originally Posted by satchmo

As far as I can see the v1a attachment is exactly the same as the original attachment?

You should be able to enter values in cells AZ19:AZ34 and see the colors of the cells to the right of them change.

I'll attach it to this message, just in case.

Does that help?

I attached version 1b to this message.

When you change the value under the torque label in AZ17, the two cells to the left of of the input cell (in columns AX and AY) will adopt the colors of the matching scheme.

Let me know if you have any more issues or questions.

(and, no...I don't work for Microsoft. I'm just fortunate that they noticed my work and granted me an MVP Award.)

wow!

I like that Ron , not that I have any use for colour coding my files but the conciseness (to me anyway) & principles of your coding is great.

If Stan is copying & pasting multiple torque values (target.rows.count>1) at once he will have problems with the colours because the Sheet Change code recolours the entire "target" not just "ccell'.
You have acknowledged/allowed for this in your code for clearing the formatting but not in the code for applying the colour formatting.

For Stan's benefit, this can be allowed for by changing...

Please Login or Register  to view this content.

Rob

I should have caught that......Thanks.

no prob's :-)
- Hopefully it solves Satchmo's problem.

I was just being nosy, seeing what the content of the thread was that was causing such a large number of replies & happened to spot it.


Rob