Im making a map of pick locations of a warehouse.
Im looking for a function like conditional formatting, though i need 10
different formats.

one location is 9 cells in one column but can vary of design, chosen by the
colour code in 'sheet2'

These are the different designs:
Sheet1: (the map)
| A | B | C | D | E |
1 | | |C2 | | |
2 | | |C1 | | |
3 | |C |C | | |
4 | | |B2 | | |
5 |B | |B1 | |B |
6 | |B |B | | |
7 | | | | | |
8 | | | | | |
9 |A |A |A |A | |
10 |AA001|AA002|AA003|AA004|AA005|

in row 10 i have the location name.
The type of design is chosen by the colour-code in sheet2 by an IF-statement .

sheet2 (table of colour codes)
(column B with list validation("Blue","Yellow","Green","Red","Gray"))
A B
AA001 Blue
AA002 Yellow
AA003 Green
AA004 Red
AA005 Gray

As you see here, Location AA001 is blue, meaning A1:A9 should have a blue
background.

If a location is used, i want to have a lighter colour.

sheet3 (locations used)
A B
AA001A Yes
AA001B No
AA002A
AA002B
AA002C

AA001A (A6:A9 in map) should be light blue.
AA001B (A1:A5 in map) should be dark blue.

If i change B1 in 'sheet2' to Yellow, the location AA001C is added to the
map and to 'sheet3'.

Then A1:A9 should vary of light and dark yellow, depending if the location
is used or not.

Ok. I dont know if i explained this good enough, but help would be greatly
appreciated!!

Thanks!
Mats