Go Back

How to Combine Conditional Formatting with an IF Statement

Read time: 29 minutes
One of the methods to achieve Conditional formatting based on a custom formula is using the IF statement. The IF function works on the IF/THEN/ELSE condition syntax. For example, “if the given argument is TRUE, THEN return this value, ELSE that value.” The IF function performs a logical test and returns one value for a TRUE result and another for a FALSE result.



Combine conditional formatting with an IF statement

Syntax 

=IF (logical_test, [value_if_true], [value_if_false])

But in conditional formatting, IF/THEN/ELSE syntax cannot be applied in a single rule. Conditional formatting is applied using IF/THEN logical test only. It must return TRUE for conditional formatting to be applied.

For example, if you want to apply conditional formatting using a condition that “If a cell value is greater than a set value, say 100, then format the cell as RED, else format the cell as GREEN”. So, you can see that it requires two rules to perform the conditional formatting, one for greater than 100, and one for less than 100.

You can apply more than one condition by creating more than one rule in conditional formatting. You can also use logical functions like AND and OR to create a rule set and apply conditional formatting in Excel.

Examples using conditional formatting with IF/THEN conditions

Let’s look at the scenarios to see how to use IF/THEN logical test in conditional formatting to highlight the targeted values. If you want to highlight the invoices in data range of A2:C13 which are due in the month of April, then you need to test the IF/THEN logical condition on date range in column B, if the month is equal to April, by using following custom formula in conditional formatting.=MONTH($B2)=4

First, select the data range A2:C13, then go to:

Conditional Formatting (on the Home tab) > New Rule> Use a formula to determine…> Enter the above formula in Edit Rule Description window> Choose the Format Fill to preview and press OK

How to Combine Conditional Formatting with an IF Statement

Please note that you have to fix the column by making it absolute using $ sign with it, and keep the row number free or relative to change. Hence, the formula will check each row of the specified column in the selected range, test the IF/THEN logical condition and will return TRUE and FALSE.

This rule will be evaluated in all active cells of locked column B one by one ignoring other cells in columns A and C. When the MONTH function in a cell of column B returns number 4 (April), the rule will return TRUE for all the active cells in that row and conditional formatting will be applied to that entire row as shown below.

How to Combine Conditional Formatting with an IF Statement

Now, if you want to conditionally highlight the two largest amount invoices due in the month of April, then you can achieve this by creating a rule set based on array formula using AND, LARGE and IF statement as per the following syntax.

=AND(criteria,$C2>=LARGE (IF(criteria, values),2)) =AND(MONTH($B2)=criteria,$C2>=LARGE (IF(MONTH(date)=criteria, amount),2)) =AND(MONTH($B2)=4,$C2>=LARGE(IF(MONTH($B$2:$B$13)=4,$C$2:$C$13),2))

By applying this custom formula, you can highlight the two largest amount invoices in the month of April. LARGE with IF function will generate a series of values and compare them with each value in column C. AND function will test the logical conditions in each cell of column B and C both one by one and will return TRUE where both conditions will be met. Have a look below.

How to Combine Conditional Formatting with an IF Statement

Need some additional help with Conditional Formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

1) I'm trying to use conditional formatting to highlight the lowest value in column D if the value in column C = "yes" and only if there are no other instances of that value. For example, if the lowest value in column D is 3 and the value in column C = yes but there is another instance of a 3 in column D and a "yes" in column C, then no cells should be highlighted. I have a conditional formatting formula built that is highlighting the lowest value in column D but doesn't take into account that the value in column C must = "yes". 2) If step 1 above is possible, can the conditional format formula in column D then be copied to column E?
Solved by Z. S. in 30 mins
Conditional Formatting Formula Help: On sheet "Anna Peruzzi" I have a list of names in column A. If that name appears in column B of sheet "June" I would like the background of the cell in column A of sheet "Anna Peruzzi" to be highlighted green.
Solved by T. L. in 26 mins
Could someone help with a conditional formatting formula please. I have a worksheet where i would like a row to be highlighted depending on a couple of conditions. The sheet is K to Y in M is the leave date of the staff member then in Q there is a drop down list containing 3 options Deleted, Proxy for 1 month and Proxy for 3 months If the option Proxy for 1 month is selected then i would like the row highlighted when the date in M is one month old If the option Proxy for 3 months is selected the i would like the row highlighted when the date in M is three months old. If deleted is selected then do nothing. Just cannot get the date thing!
Solved by K. J. in 24 mins
Excel 2016. I need the conditional formatting formula to adjust to each row the cell is on. I used the format painter but this copies the same formula to all cells. need it to adjust to every row.
Solved by D. Q. in 17 mins
Greetings! I'm trying to copy the following conditional formatting formula to different cells, but I need the cell reference to change to reflect the cells where they're being pasted, ie. S16:T16, S17:T17, S18:T18, etc. I'm looking for a quicker way to input the formulas so that I don't have to put them in individually, as there are hundreds that need to be inputted. If you can assist, I would greatly appreciate it. Also, the point of this formula is to highlight the adjacent cells that have the same value. =COUNTIF($S$15:$T$15,INDIRECT(ADDRESS(ROW(), COLUMN(), 4))) > 1
Solved by G. H. in 27 mins
Hello. I'm looking for a conditional formatting formula to highlight a range of cells based on value of another cell range. I'm using Google Sheets.
Solved by S. H. in 23 mins
Hi I am just looking for help with my conditional formatting formula =AND(LEFT(F$4,4)="1350",F9>F34). I want to add a condition to it that if Cell F5=buy, then the formatting does not apply. Can you please assist?
Solved by Z. C. in 20 mins
Hi, I need a conditional formatting formula that will highlight any cell that has changes made to it. I tried =$a1<>$a1 but it's not working. Do you know if there is a formula?
Solved by M. D. in 26 mins
How can I modify my conditional formatting formula that I currently have applied to cells E7 - E16 to instead make an entire given row up to column H red if a cell between E7 - E16 contains a # value that is less than the displayed day # value within the date of the month in cell E4?
Solved by A. E. in 30 mins

11
Leave a Comment

avatar
11 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
9 Comment authors
AdilSamBenDamienKevin Recent comment authors
newest oldest
Carol
Guest
Carol

Hi there Im trying to apply conditional formatting to cells that do not contain a formula. I am using excel 2010 so Ive used a macro to create the =ISFORMULA function but the conditional formatting is not working if the formula returns FALSE

Sam
Guest
Sam

I am using duplicate control in excel through Conditional Formatting, total 18,500 nos. of Rows are existing in the sheet. Filter with Highlight Color is not working now. Please suggest me the best way to check duplicacy..

Sam
Guest
Sam

I want to copy Conditional formatting rules, but also the change the rule it applies to based on the cell it is in.

Brady
Guest
Brady

Trying to use conditional formatting to color code cells that are greater or less than another cell. However there is text in the cells and the conditional formatting isn’t correctly color coding values that are higher or lower. Any ideas?

Carl
Guest
Carl

I have a formula of color changing (conditional formatting), just need someone to help fix it!

Daniel
Guest
Daniel

I want to be able to use conditional formatting to highlight a column of different values in comparison to another column, but all the values vary. I know how to use conditional formatting to highlight a value over or under a set amount but not a varied one?

Kevin
Guest
Kevin

How to Use Conditional Formatting to Change Cell Background Color Based on Cell Value not for only 1 cell but all the cells depending upon the cell value entered. for example I have four status as 1 )open 2) Resolved 3) Overdue 4) hold. I want the cell should change color as green if resolved and red as overdue and open as brown and hold as yellow.

Damien
Guest
Damien

Hi, I’d like to create format table for work due date 7 days before allot required(like conditional formatting) with different scenarios of production stages. Kindly please help me

Ben
Guest
Ben

I am struggling to create a conditional formatting formula that is dependent on the contents of another cell. which changes color depending on the proximity of the contents of the second cell

Sam
Guest
Sam

I have an issue with my pivot table. I am trying to set the conditional formatting on my pivot. The data in my pivot will change daily. I would like my rule to apply to my data as it adjusts daily. I set my rules but when I filter or make any changes to the data my conditional formatting rule is taken off. How can I get this conditional formatting to stay in place?

Adil
Guest
Adil

I need help with conditional formatting, I cannot seem to make the rules work and apply to the whole sheet.