CAT,

Try this: Name your two column color lookup table (AM1:AN29 ) ColorTable. Then copy the code below,
right-click the sheet tab, select "View Code" and paste the code into the window that appears. I'm
not sure of your worksheet structure, but if the cells in column G match the values in AM, and AN
has valid color index numbers, then it should work.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Calculate()
Dim myCell As Range
Dim myColor As Variant

On Error GoTo ErrHandler

For Each myCell In Intersect(Range("H:Q"), Range("H2").CurrentRegion)
If myCell.Value < "" Then
myColor = Cells(myCell.Row, 7).Value
myCell.Interior.ColorIndex = _
Application.VLookup(myColor, Range("ColorTable"), 2, False)
End If
NotColor:
Next myCell

Exit Sub
ErrHandler:
Resume NotColor:

End Sub

"CAT" wrote in message
...
Hello everyone,
I am a fairly new user of Excell 2007, but have received some precious help
on here re other aspects of my worksheet layout but this one is a tough one
and I am not sure to which thread this query should be addressed (the
Worksheet Functions or the present Programming) so I will let you guys decide
whether I need a new Function or a VBA Code to resolve it, and here goes:

Outside my main worksheet, I have set up a LookUp table with 2 cols starting
with cell AM1 and finishing with cell AN29 with precise values which are used
in the worksheet proper.
Next to those 2 cols, I have set, in col AO, a list of conditional
formatting comprising 3 rules (all ticked "if true") relating to each of the
values listed in cols AM and AN and applying to $AO$ right through to $AO$29.
The Conditional Formatting will change - or not - the background colour of
the cells in a number of columns of the main worksheet, according to the
value entered into those, and is itself related to the lists in the LookUp
table.
*Example (of LookUp table)
Col AM Col AN Col AO (Cond Format)
Row 1 20.00 10.00 Cell value greater=20.00 no colour
Cell value less than or=10.00 med blue
Cell value between=16 and=11 med green
Row 2 18.00 9.00 Cell value =18.00 no colour
" " <=9.00 med blue
" " between=14 and=10 med green
Row 3 16.00 8.50 Cell value =16.00 no colour
" " <=8.50 med blue
" " between=12 and=9 med green
.../... etc. etc., until (with only 2 formats):
2.50 1.50 Cell value =2.50 no colour
Cell value <=1.50 med blue
2.25 1.38 Cell value =2.25 no colour
" " <=1.38 med blue
.../... etc., etc., until last entry (row 29)
1.50 0,83 Cell value =1.50 no colour
" " <=0.83 med blue
In total, 29 entries in each col AM and AN and 3 to 2 Cond Formats in col AO
for each of the 29 entries.
*NB. There is a slight problem with my present Cond Formatting in that the
cells background colour is returned as med blue until I enter a value which
changes it according to the above rules (and I realise why that is), but I
would rather have the cells staying with no bacground colour when blanks and
only change to green or blue (or no colour) according to the above listed
formatting criteria when I enter a value, so that the colored cells (the
important part) would show up when the relevant data is entered.

*What I have been doing:
There are 12 cols involved in the main worksheet.
Col G - Manual entry data
Random entry per list in col AM of LookUp table.
Col H to Col Q - Blank cells
Conditional Formatting applied, based on the values entered in col G, prior
to entering any data.
Col R - Calculated data (via a LookUp Function)
returning the equivalent values listed in col AN of the LookUp table and
linked to data entered in col G.
Example:
Col G Col H to Col Q Col R
Row 7(I enter)16.00 I copy and paste the CF (Returns) 8.50
corresponding to the
value of 16.00 in col AO
of LookUp table
Row8(I enter)9.00 I copy and paste the CF (Returns) 5.00
corresponding to the
value of 9.00 in col AO
of LookUp table

...and so on for each row, which can amount to 400+. It's a long and tedious
process and mistakes can be (and are) made e.g. wrong formatting applied, or
missing some columns, cells, etc.

For the final part of the work, I then enter data in each cell of cols H to
Q, which will change colours according to the above mentioned criteria.

*What I would like to achieve:
Have either a Function or a VBA Code that will automatically conditionally
format cols H to Q, based on the value entered in col G and the formatting
described in the LookUp table, taking on board the NB re my present
formatting, so that all I would have to do would be to enter data in Col G,
and later on in cols H to Q, for the relevant cells to show up in colour (or
not) as the case may be.

I have absolutely no knowledge of VBA Code but have a book (the Dummies kind
- J Walkenbach) and am willing to learn, but will need to be coached all the
way to make this one work. It will be a learning curve!
I am very grateful for any help and give my thanks in advance to anyone who
will take the time to read and digest this rather long and convoluted query.
Cat