Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Cond Formatting of multiple columns based of values of 1
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting or Grouping multiple rows based on unique values | Excel Discussion (Misc queries) | |||
Conditional function row formatting based on columns values being | Excel Discussion (Misc queries) | |||
Cond. formatting, based on other cell values? | Excel Discussion (Misc queries) | |||
Cond. Format Data Bars of range based on values of another range | Excel Worksheet Functions | |||
How to lookup and sum multiple values based on multple columns | Excel Programming |