Microsoft Excel: How do I highlight rows based on value range/text of two different columns?

I'm not big on conditional formatting, but here is my attempt: I broke it into two different conditional formatting rules. Blue Row Highlight for 'Sale' in C and >1 in H Select the columns you want to receive the highlighting, I chose A through H. On the ribbon under the Home section, select Conditional Formatting New Rule Use a formula to determine.... Enter =AND($C1="SALE", $H1>1) Click format Add formatting you want Click OK May have to go to Manage Rules and hit apply Red Row Highlight for not 'Sale' in C and < 1 in H Change Step 5 to =AND($C1<>"SALE", $H1<1,$H1<>"") (Note I added the last condition $H1<>"" so it wouldn't highlight all the blank lines.. it just says that the cell in H is not empty)

If you are using a Mac with Office 2011, these are the steps Step 1 Highlight all the cells you want to be color coded On the ribbon Home > Conditional Formatting > New Rule Change 'Style' to Classic Change 'Format only top or bottom ranked values' to 'Use formula to determine which cells to format' Enter this formula =AND($C2="SALE",$H2<1) Format with>Custom Color>Fill>Change color to Blue. Click ok. Step 2 Highlight all the cells you want to be color coded Home > Conditional Formatting > New Rule Change 'Style' to Classic Change 'Format only top or bottom ranked values' to 'Use formula to determine which cells to format' Formula =AND($C2<>"SALE",$H2<1 Format with>Custom Color>Fill in the tab bar>Change color to Red Click ok

Here is a post i wrote that explains in detail how to do it: http://blogs.office.com/b/microsoft-excel/archive/2010/05/24/track-the-soccer-world-cup-scores-with-excel-174.aspx

I would do it in the following manner: Highlight the first row of your data. I'm assuming it is Row 2. Select Conditional Formatting -> New Rule Rule Type: Use a formula to determine which cells to format Enter in the formula =AND(TRIM($C2)="SALE",VALUE($H2)<1) Click on format and select fill color blue. Click OK twice Click Conditional Formatting -> Manage Rules (Do not change your selection before you do this) Click New Rule Rule Type: Use a formula to determine which cells to format Enter in the formula =AND(TRIM($C2)<>"SALE",VALUE($H2)<1) Click on format and select fill color red. Now ensure that the first rule you set is the top in the order. If it's not just select that rule and click the button with the up arrow in it. Now on both rules check the box that says "Stop if true". This will ensure that no additional styles are applied if multiple conditions happen to be true. Now replace the Applies to Range on both rules with your full data range. Let's say you have 10 rows of data starting at Row 2 the new applies to Range will be $2:$11. That should work. Let me know if you have any issues.

If you can replicate 's answer, then u'll be able to solve it. You can also refer this http://bit.ly/YCfDvw