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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Cond Formatting of multiple columns based of values of 1
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Cond Formatting of multiple columns based of values
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 "Bernie Deitrick" wrote: 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 |
Reply |
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 |