Conditional Formatting

[RodneyC]

My rows currently have a colored background determined by selecting the cells and choosing the desired color. I need to take it a step further and have a background pattern applied based on meeting this condition:

1. Look at columns I – M. If any cell in those columns, in any row between 10 and 500 has a value of less than 500, fill the cell with the selected pattern.
2. To take the above concept a step further, if any row has more than four of the columns where the above condition is met, choose a different pattern for cells on columns I – M for that row.

If only the first item above can be done, I can work with that. If I can have he second one as well, that much the better.

Thanks in advance

 

[etaf]

should be able to use conditional formatting
2 rules
in order
Select the range
I2:M1000
then a formula

= AND( I2<>"", I2 <500)
fill the colour

2nd rule
=COUNTIF($I2:$M2,"<"&500) =4

Book1
	I	J	K	L	M
2
3	2		5
4
5	2
6	4	5	6		8
7
8	5
9	5	6		7	8
10
11
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:M18	Expression	=COUNTIF($I2:$M2,"<"&500) =4	text	YES
I2:M18	Expression	=AND( I2<>"", I2<500)	text	YES

1st Rule
for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
I2:M100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:

=COUNTIF($I2:$M2,"<"&500) =4

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

repeat for 2nd rule
= AND( I2<>"", I2 <500)

use STOP IF TRUE

 

[RodneyC]

Thank you for your well thought out and detailed explanation. It's working, somewhat. It's filling in (conditioning) cells that don't meet the criteria. Does it make a difference if the data is being pulled from a pivot table?

 

[etaf]

It's filling in (conditioning) cells that don't meet the criteria. Does it make a difference if the data is being pulled from a pivot table?

can you give some examples
Are the numbers TEXT ?
Not sure about the pivot table
how is it pulled ?

 

[RodneyC]

Take a look at these screen shots. Although you can't see the full formulas, I copy/pasted yours exactly, other than changing column "M" to "N". The results are on the right.

The green circle shows what I want to display if a value is less than 500. Note that it has the lighter pattern. If four or more values in the same row are less than 500, I want the background to be the darker pattern.

The red circles shows examples of where the values are less than 500 and the should have the lighter pattern, but there is none.

The blue circles show where the values are greater than 500 so they should have no pattern.

The pivot table is on another tab which is pulling data from yet another tab. The cells where the data is coming from is formatted as "number" then "general"

I appreciate your help here.

Thanks

 

[etaf]

so the row you selcted was from row 10
But you told the conditional formatting to start at row 2
Note my selection row is the same as the formula

So conditional formatting will look at row 10 to format - BUT will be testing the contents of row 2 - so you are 8 out

try changing the formulas to start at the same row as the selection

I10:N427
So the formulas need to be
=COUNTIF($I10:$M10,"<"&500) =4
= AND( I10 <>"", I10 <500)

also the order ,
the count - first and stop if true you can click on the formula and use the arrows to move up/down

 

[RodneyC]

I got it to work, thank you.

 

[etaf]

you are welcome,
did you get it to work by changing the row number as suggested, may help others searching

 

[RodneyC]

you are welcome,
did you get it to work by changing the row number as suggested, may help others searching

Yep, that's what it needed.