# Conditional Formatting

#### Nanaia

##### Board Regular
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

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
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
Try
=SUMPRODUCT(--(F19:F48>M19:M48))>0

#### Joe4

##### MrExcel MVP, Junior Admin
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
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
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
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
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
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
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]

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...