Advanced Conditional Formatting: Turn On & Off Cell Color with CHECKBOXES in Excel

Welcome to the Excel Jet Consult blog post. In this post, I'm going to walk you through how to create advanced conditional formatting using the new rule and then use the checkboxes to turn off and on the highlighting of the rows. Let's get started.

Sample Data

In the picture below, we've got a sample sales data with a handful of columns. We want to apply the advanced conditional formatting to highlight the entire rows with green, yellow, and red fill colors when Sales Amount is greater than or equal to 50000, less than 50,000, and greater than or equal to 25,000 and less than 25,000, respectively.

Advanced Conditional Formatting

To achieve this, we are going to use the AND logical function that requires that all the arguments of the AND function must be true.

Green Color: Sales Amount Greater Than or Equal to 50,000

• To apply the Conditional Formatting, Select all the data, excluding the headers.
• In the Conditional Formatting drop-down of the home tab, select New Rule.
  
• In the New Formatting Rule dialogue box, select use a formula to determine which cells to format the rule.
• In the Format values where this formula is true box, execute this formula: =AND($D5,$C5>=50000)
• Click Formatting and choose Green color in the Fill and Bold and Italic and White Font in the Font tabs. We can see a preview of the formatting.
• Click OK

Yellow Color: Sales Amount Greater less than 50,000 and greater than or equal to 25,000

• In the Conditional Formatting drop-down of the home tab, select New Rule
• In the New Formatting Rule dialogue box, select use a formula to determine with cells to format the rule.
• In the Format values where this formula is a true box, execute this formula: =AND($D5,$C5<50000,$C5>=25000)
• Click Formatting and choose a yellow color in the Fill and Bold and Italic font in the Font tabs.
  
• Click OK

Red Color: Sales Amount Greater less than 25,000

• In the Conditional Formatting drop-down of the home tab, select New Rule
• In the New Formatting Rule dialogue box, select use a formula to determine with cells to format the rule
• In the Format values where this formula is true box, execute this formula: =AND($D5,$C5<25000)
• Click Formatting and choose a red color in the Fill and Bold and Italic and white font in the Font tabs.
  

In the picture below, we checked the boxes for Sales Amount that is greater than or equal to 50,000, and the rows were highlighted in green. The same applies to Sales Amounts less than 50,000 and greater than or equal to 25,000 (yellow fill color) and Sales amounts less than 25,000 (red fill color).