Conditional Formatting Table Rows Based off a Cell Value that contains IF Function

Question

The IF function may not be relevant, however, I want to add conditional formatting to an entire table row based off the corresponding value in the B column so that way every time we enter a new row of data, the IF function is carried over as well as the conditional formatting.

If the value in column B is Over 45 Days then the entire row will be red, if Open then yellow, if Closed then green.

Any ideas/

Answer 1

I have the opinion that you are quite new to this Conditional formatting so I will provide step by step instructions.

Rather than attempting to add the conditional formatting to the added new rows, assign the conditional formatting to the entire columns and it will then only apply itself to the rows that meet the required conditions.

Firstly clear any previously created rules from the selected columns as follows

• Select entire columns A and B (Or more columns if table occupies more columns than your example)
• Select Conditional formatting
• Select Clear Rules -> Clear rules from selected cells

Create the 1st rule

• Select entire columns A and B (Or if more columns if table occupies more columns than your example)
• Select Conditional formatting
• Select Manage rules (NOT New rule)
• Click New rule button
• Select "Use a formula to determine which cells to format"
• Enter the following formula in the field (Note that it commences with equals sign)

=$B1 = "Over 45 Days"

• Click Format button
• Select Fill tab (at top of dialog)
• Select Red color
• Click OK and OK again
• You will be returned to the previous dialog
• Click Apply

Create the 2nd rule

• Click New rule button
• Select "Use a formula to determine which cells to format"
• Enter the following formula in the field***(Note that it commences with equals sign)***

=$B1 = "Open"

• Click Format button
• Select Fill tab (at top of dialog)
• Select Yellow color
• Click OK and OK again
• You will be returned to the previous dialog
• Click Apply

Create the 3rd rule

• Click New rule button
• Select "Use a formula to determine which cells to format"
• Enter the following formula in the field (Note that it commences with equals sign)

=$B1 = "Closed"

• Click Format button
• Select Fill tab (at top of dialog)
• Select Green color
• Click OK and OK again
• You will be returned to the previous dialog
• Click Apply
• Click OK to close the Conditional formatting dialog

Feel free to get back to me if any problems with the guidelines.