Excel 2007 : Conditional formatting based on multiple criteria

**rmexcel** · 01-20-2011, 11:48 AM

Hello, I was hoping someone could help me with a conditional formatting question in Excel 2007.

A little background: once a week we use some third party software to pull data from our database and create an inventory report in Excel. This report has multiple lines for each item in inventory and followed by totals on every 15th row. This sheet will generally be about 10,000 rows long.

I tried to color code this report to make it easier on myself to quickly scan through by highlighting the relevant columns and conditional formatting based on numerical ranges (i.e. red if less than or equal to zero, yellow from 0 to 10, red if greater). The problem is that this highlights a lot more than what i need and makes it even more difficult to scan through quickly.

So what i ended up doing was to highlight every 15th row using a formula in conditional formatting [=AND(MOD((ROW()-1),15)=0) --ROW()-1 because the first row is headings]. This highlights just the totals on every 15th row, but the problem is, now i can't figure out how to get the value of each cell to format based on numerical ranges.

Is something like this even possible?

Thanks in advance for all of your help.

Summary: I want to highlight every 15th row of data red if <= 0, yellow if between 0 and 10, and green if >= 10.

**NBVC** · 01-20-2011, 11:55 AM

You have to make 3 different conditional formulas now, and for each add to the AND (which you didn't need if it was just the one condition, btw)

e.g

=AND(MOD((ROW()-1),15)=0,$A$1<=0)

=AND(MOD((ROW()-1),15)=0,$A$1>0,$A$1<10)

=AND(MOD((ROW()-1),15)=0,$A1>=10)

assuming column A is being tested for the data condition.

**rmexcel** · 01-20-2011, 01:01 PM

Wow, thanks for getting back to me so quickly.

A couple of things:

First, I included the AND because I knew there would be multiple conditions, I just didn't know what else to include I guess, so thanks for filling in the blanks in my excel knowledge.

Second, I would be using this for columns K through Y, so is there a way highlight them all at once and use one master formula? Or do i have to go through each column and make the appropriate changes each time? I tried a couple of things but couldn't seem to figure this one out either.

You are a life-saver! Thanks again.

**NBVC** · 01-20-2011, 01:09 PM

You can use the formula I gave as "master formulas" after you have selected columns K to Y. You would need to replace the $A1 reference to the top-most cell reference of your select where the criteria is to be searched and matched..

The conditional formatting will adjust the 1 in $A1 to match row you are in... the A will remain the same vertically and horizontally because of the $ in front.