Conditional Formatting - Multiple Conditions

[kevin0226]

Hi. I need to highlight column cells (rows 2 and on) that 1) are non-blank, 2) contain a number that is less than the number in that column's first row.
And then, how to spread that formatting to all columns of the spreadsheet?

5	3​	4​	4​	4​	3​	5​	3​	4​
7	4​	6​	6​	4​	3​	5​	3​	5​
6	3​	4​	5​	4​	3​	5​	4​	4​
6	4​	5​	3​	5​	2​	4​	5​	5​
4	2​	4​	4​	3​	3​	5​	5​	3​
6	4​	5​	5​	5​	3​	5​	4​	4​
6	3​	4​	4​	4​	3​	6​	2​	4​
5	3​	5​	3​	3​	3​	4​	3​	4​
5	4​	4​	5​	4​	3​	5​	4​	4​
4	3​	5​	5​	5​	2​	5​	5​	4​
5	4​	4​	5​	5​	3​	6​	2​	4​
6	3​	5​	5​	3​	3​	4​	3​	5​

Thanks!!!

 

[Asbestos_Jen]

Try this:

 

[Joe4]

Selecting the range starting in cell B2 down to the last row and column with data.
Then go to Conditional Formatting and enter this Conditional Formatting formula in:

=AND($A2>0,B2>0,B2<$A2)

and choose your desired highlighting color.

The results should look something like this:

 

[kevin0226]

Try this:
View attachment 108846

That worked! Thank you so much! Bonus question: How to count the number of highlighted (colored) cells in each row?
And bonus question #2: How to count the number of consecutive colored cells in a row (e.g. A8 and B8)?

 

[Joe4]

That worked! Thank you so much! Bonus question: How to count the number of highlighted (colored) cells in each row?

Have a look at the COUNTIFS function. You should be able to use the same conditions that are being used in the CF formula.

 

[kevin0226]

Have a look at the COUNTIFS function. You should be able to use the same conditions that are being used in the CF formula.

 

[kevin0226]

Can't get it to work. would you please write out the formula? I tried using this: =COUNTIF(A4<>"",A4<A$1), but no go. I am highlighting the cells with light green (excel code 4).

Thanks!

 

[Joe4]

Microsoft/Excel actually does a good job documenting all their functions. They contain detailed explanations and examples.
I could do it for you, but I would like you to take a try at it (I usually like to try to help people becomes "self-sufficient").
If you look at the very last example, it shows you how to do "less than" conditions using a value from a cell (almost exactly what you are trying to do).

COUNTIFS function - Microsoft Support

How to use the COUNTIFS function in Excel to apply criteria to cells, across multiple ranges, and count the number of times all criteria are met.

support.microsoft.com

Take a run at it, and see how you do!
If you get stuck again, post back with the formula you tried, and we will show you how to fix it up.