none
Multiple Conditional Formating RRS feed

  • Question

  • I have an interesting challenge.  I know how to create conditional formatting comparing one cell against another cell.  What I am trying to do is to compare the values in from one column against one or two other columns.  My goal is this - if the values from column1 and less then the values in column two, the field where the condition is met in column 1 should be shaded in red.  If the value in column 1 is between the values in column2 and column2, then the field where the condition is met in column 1 should be shaded in yellow.  Finally is the value in column 1 is greater than the value in column 3, then the field where the condition is met in column 1 should be shaded in green.  Because my field contains nearly 1000 records, I figure there has to be some way.  I am thinking perhaps this could be done using a VBA procedure but I am not sure how.  

    Here is a sample of how my data looks:

    The condition should apply to Unit % based on the comparisions to the two following columns. Any suggestions would be greatly appreciated!

    Tuesday, November 3, 2015 7:49 PM

Answers

  • Let's say that Unit % is column B, AMC % is column E and VBP Treshold is column K, and that the data begin in row 2.

    Select B2:B1000 (or however far down the data go). I'll assume that B2 is the active cell within the selection.

    • On the Home tab of the ribbon, select Conditional Formatting > New Rule...
    • Select 'Use a formula to determine which cells to format'.
    • Enter the formula =AND(B2<C2,C2<>"")
    • Click Format...
    • Activate the Fill tab and select red.
    • Click OK twice.

    Without changing the selection, repeat the above steps, but with the formula =AND(B2>=C2,B2<=D2,C2<>"",D2<>"") and yellow as fill color.

    Repeat them again, with the formula =AND(B2>D2,D2<>"") and green as fill color.

    In the formulas, 2 is the row number of the active cell - change if this is different.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by seebert Tuesday, November 3, 2015 8:33 PM
    Tuesday, November 3, 2015 8:03 PM

All replies

  • Let's say that Unit % is column B, AMC % is column E and VBP Treshold is column K, and that the data begin in row 2.

    Select B2:B1000 (or however far down the data go). I'll assume that B2 is the active cell within the selection.

    • On the Home tab of the ribbon, select Conditional Formatting > New Rule...
    • Select 'Use a formula to determine which cells to format'.
    • Enter the formula =AND(B2<C2,C2<>"")
    • Click Format...
    • Activate the Fill tab and select red.
    • Click OK twice.

    Without changing the selection, repeat the above steps, but with the formula =AND(B2>=C2,B2<=D2,C2<>"",D2<>"") and yellow as fill color.

    Repeat them again, with the formula =AND(B2>D2,D2<>"") and green as fill color.

    In the formulas, 2 is the row number of the active cell - change if this is different.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by seebert Tuesday, November 3, 2015 8:33 PM
    Tuesday, November 3, 2015 8:03 PM
  • YOU ARE BRILLANT!!!  Thank you so much it worked perfectly!!!
    Tuesday, November 3, 2015 8:33 PM