Conditional Formatting

[Nanaia]

I want to utilize conditional formatting and need assistance with a formula to use in it. I want it to turn the text in cell M16 bold, red, italic if any of column M equals less than its corresponding line in column F. I know how to write a formula for one cell but not for a whole column. For example, if F19 is 20 but M19 is 4, then M16 turns red, if F20 is 8 and M20 is 6, etc. Basically, if any cell from M19:M48 is less than F19:F48 in the same row, then turn the text in M16 red. If column M is consistently more than F, then M16 stays its neutral color (white). Oh, and it cannot be with a Macro. Folks here block those because they don't understand them.
I hope this makes sense. You folks are always so helpful. It's appreciated!

 

[Joe4]

Select all the rows in column M that you want to apply this to, and then write the Conditional Formatting formula as it applies to the FIRST row in your selection.
For example, if you selected to apply the CF to range M16:M100, enter this CF formula:

=$M16 < $F16

and choose your formatting options.

 

[Fluff]

Try
=SUMPRODUCT(--(F19:F48>M19:M48))>0

 

[Joe4]

I think I may have been confused and misunderstood. I was thinking you wanted a line-by-line Conditional Formatting based on the values in that particular row.
But on re-reading more closely, it looks like you want M16 to be CF if ANY row in 19:48 meets those conditions.
In that case, Fluff has given you a formula that would do that.

 

[Nanaia]

Each line item is different. If applying it to the first row won't it only look at that first row and ignore the rest? (not being argumentative, just trying to understand)

 

[Nanaia]

Will SUMPRODUCT give the correct results if the sum of column M equals more than column F however one of the cells in column M is less than its neighbor in column F? For example, the most common number for column M is 48, however most of the numbers in column F will be less than 24. However occasionally column M will have a value of 3.969 or a similar small number. Those are the times I need the conditional formatting to come into play. I'm wondering if I should apply it to each individual cell in column M and a separate one for M16 so that if any cell in column M is red then M16 also turns red?

 

[Fluff]

The sumproduct works on a row by row basis

with this data the result is 1 (visible in M17) which comes from row 21

Excel 2013/2016
	F	M
17		1
18
19	10	10
20	2	2
21	24	15
22	24	48
23	24	48
Sheet1

 

[Joe4]

Fluff's formula will apply the Conditional Formatting if column F is greater than column M for ANY row (between 19 and 48).
So as long as one row meets that requirement (column F greater than column M), it will be applied.
Isn't that what you are asking for?
Did you try it out?

 

[Nanaia]

Yes I tried it. It works beautifully. Thank you. I thought that SUMPRODUCT was going to sum each entire column and compare sums.

 

[Joe4]

It is summing the expression:

[COLOR=#333333]F19:F48>M19:M48

when applied to each row.

If that evaluates to TRUE, it returns 1. If not, it returns 0.
So it sums up a bunch of 1s and 0s. If it returns anything other than 0, then it means that at least one row is TRUE.

[/COLOR]