Conditionally format a cell based off a selection of drop down data in a column

Related searches

I created an apparatus maintenance board for my fire department. Each apparatus has its own tab with the name of that apparatus in cell A1.

Each row below that has room to list issues for that apparatus with column D being a drop-down list for the current progress. I want to be able to format the color of the apparatus name is cell A1 based on multiple values from the drop-down list in column D.

Example, when a maintenance issue is not completed we select from the drop-down list in column D of either SERVICE SCHEDULED, PENDING, PARTS ORDERED, etc. I want the apparatus name in A1 to turn red if any cell in column D contains one of those statuses.

With A1 selected, go into Conditional Formatting and Custom formula is.. and enter

=SUMPRODUCT(countif(D:D,{"SERVICE SCHEDULED", "PENDING", "PARTS ORDERED"}))

and select red fill colour.

Advanced Excel Conditional Formatting Show List and Colors, Conditional Formatting an Excel Drop Down List - Learn how to some conditional formatting to the drop down data validation list. when a job is completed, check off items from a list or to evaluate risk in a Select Use a formula to determine which cells to format 0 out of 5 stars (based on 0 reviews). Select Use a formula to determine which cells to format. Type in the Formula =$A4=”low”. This formula will ensure only the column is absolute. Go to Format > Fill then select a color of your choosing. Click OK. This is how our new set of rules will look like: Now our table now has conditional formatting applied!

custom formula:

=COUNTIF(D:D,{"SERVICE SCHEDULED", "PENDING", "PARTS ORDERED"})

How to highlight rows based on drop down list in Excel?, Conditional Formatting and Dropdown Menus For example, selecting Cancelled from a list should change the cell background colour to Select the cells you want to apply the data validation to (this maybe on a new sheet). When we want to format a cell based on the value in a different cell, we will use a formula to define the conditional formatting rule. It’s a very easy process to set up a formatting formula. First select the entire data from A3:E13 as shown below. Go to the HOME tab.

Slightly longer than Tom Sharpe's answer (and slightly less easily extensible) but possibly a little simpler since applying only a single function this formula:

=ArrayFormula(or(D:D="SERVICE SCHEDULED",D:D="PENDING",D:D="PARTS ORDERED"))

with choice of red font might suit.

Conditionally Formatting A Drop Down List, To use conditional formatting based on another cell, select New Rule option from the Home menu’s Conditional Formatting dropdown. Then select the last option which is “Use a formula to determine which cells to format” from the list. This will allow the user to select the cells on whose value we want to relate to create formatting.

Conditional formatting and drop down menus, We will be targeting this cell to perform the conditional formatting for the entire range of cells. The procedure is given below. First, write down the text “Pass” in cell C2. Select the range of cells where you want to apply the conditional formatting. Now, select the New Rule from Conditional Formatting option under the Home.

Conditional Formatting allows you to format a cell (or a range of cells) based on the value in it. But sometimes, instead of just getting the cell highlighted, you may want to highlight the entire row (or column) based on the value in one cell.

First, please insert the drop down list, select the cells where you want to insert the drop down list, and then click Data > Data Validation > Data Validation, see screenshot: 2. In the Data Validation dialog box, under the Settings tab, select List form the Allow dropdown, in the Source text box, please click button to select the values you

Comments
  • Thanks a lot! This worked perfectly. How would you define the range if the conditionally formatted cell was on a different sheet than the data?
  • You have to use indirect if it's in a different sheet =sumproduct(COUNTIFS(indirect("Sheet9!D:D"),{"SERVICE SCHEDULED", "PENDING", "PARTS ORDERED"}))