Conditional Formatting

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
222
Office Version
365
Platform
Windows, 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:

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,949
Office Version
365
Platform
Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,439
Office Version
365
Platform
Windows
Try
=SUMPRODUCT(--(F19:F48>M19:M48))>0
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,949
Office Version
365
Platform
Windows
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

Board Regular
Joined
Jan 11, 2018
Messages
222
Office Version
365
Platform
Windows, MacOS
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

Board Regular
Joined
Jan 11, 2018
Messages
222
Office Version
365
Platform
Windows, MacOS
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,439
Office Version
365
Platform
Windows
The sumproduct works on a row by row basis

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

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>F</th><th>M</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;">24</td><td style="text-align: right;;">15</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;">24</td><td style="text-align: right;;">48</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;;">24</td><td style="text-align: right;;">48</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br />
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,949
Office Version
365
Platform
Windows
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

Board Regular
Joined
Jan 11, 2018
Messages
222
Office Version
365
Platform
Windows, MacOS
Yes I tried it. It works beautifully. Thank you. I thought that SUMPRODUCT was going to sum each entire column and compare sums.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,949
Office Version
365
Platform
Windows
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]
 

Watch MrExcel Video

Forum statistics

Threads
1,095,174
Messages
5,442,830
Members
405,200
Latest member
Barkworth

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top