Conditional Formatting

Nanaia

Active Member
Joined
Jan 11, 2018
Messages
304
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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!
 
Last edited:

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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:
Code:
=$M16 < $F16
and choose your formatting options.
 
Upvote 0
Try
=SUMPRODUCT(--(F19:F48>M19:M48))>0
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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?
 
Upvote 0
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
FM
171
18
191010
2022
212415
222448
232448
Sheet1
 
Last edited:
Upvote 0
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?
 
Upvote 0
Yes I tried it. It works beautifully. Thank you. I thought that SUMPRODUCT was going to sum each entire column and compare sums.
 
Upvote 0
It is summing the expression:
Code:
[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]
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top