Automatic Cond Formatting of multiple columns based of values of 1

[CAT]

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

 

[Bernie Deitrick]

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]

Hi Bernie,
Thank you for taking the time to read my long winded query!
I have two questions for you (bear in mind I have never used VBA Code
before) so I might be testing your patience!
But first of all, I wanted to explain that my lookup table starts on cell
AM1 to AM29 and the second column on AN21 to AN29. They both contain a list
of numbers that are related to each other and are used with my LOOKUP
Function in col R to return a value in each cell of col R whenever I have an
entry in col G. None of those two columns have any reference to colour
formatting.
It is the third column that I have made up (very clumsily), entering 3
conditions (<=,>=,between... and (and listing them all as true)) using Excell
Conditional Formatting for each number listed in col M. This formatting
starts in cell AO1 up to cell AO29, but unfortunately, it stands on its own -
as in, only applies to the cell it is in (applies to $AO$1, $AO$2, etc.)
hence the copying and pasting in the worksheet.

I realise my explanations are very clumsy but I would not know how to word
it otherwise, my total lack of programming knowledge obviously shines through!

My first question is where in the spreadsheet would I name the two column
color lookup table (if it is still applicable)
My second question (quite important I think!) is where do I right-click the
sheet tab to get the View Code? Or do I go to Developer and click on View
Code there? I also take it I can copy and paste your code right into it?

I stand humbled but hopeful!
Kind Regards
Cat