Answered by:
Multiple Conditional Formating
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!
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
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
