Conditional Formatting with Multiple Cell Values

[AmberTafolla]

Hello, I am hoping someone know how to help me set up conditional color coding in excel. I am needing certain cells to populate one color if the others cells are filled out wrong and another color if the cells are filled out right.

Example:

If there is a number in cell E and cell H says F1IL and cell J isn't blank and cell P is blank all these cells turn red.

But if there is a number in cell E and cell H says (anything but F1IL) and cell J is blank and cell P has a number then all these cells turn green.

My goal is to have errors pop up when someone doesn't fill out the information correctly, but to also not have anything highlighted if there is no information entered on that line.

Thank you for your help!

[#2]

@AmberTafolla 

Let's say you want to apply this to rows 2 to 100.

Select E2:E100. E2 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=AND($E2<>"", $H2="F1IL", $J2<>"", $P2="")

Click Format...
Activate the Fill tab.
Select red as fill color.
Click OK, then click OK again.

Repeat these steps, but with the formula

=AND($E2<>"", $H2<>"F1IL", $J2="", $P2<>"")

and green as fill color.

With E2:E50 still selected, double-click the Format Painter button in the Clipboard group of the Home tab of the ribbon.

Click on H2, then on H2 and finally on P2.

Then press Esc to turn Format Painter off.

[#3]

Select the Range: Select the range of cells you want to format (e.g., E2:P2). This is the range that will change color based on the conditions.
Create a New Rule: Go to the Home tab, click Conditional Formatting, and then "New Rule".
Use a Formula to Determine Which Cells to Format: Choose this option.
Enter the Formula: This is where the logic for your conditions will go.
For the red condition: =AND(ISNUMBER(E2),H2="F1IL",ISBLANK(J2),ISBLANK(P2))
For the green condition: =AND(ISNUMBER(E2),H2<>"F1IL",ISBLANK(J2),ISNUMBER(P2))
Format the Cells: Click the "Format" button to choose the red fill color for the first condition and green for the second.
Create Another Rule: Repeat steps 2-5 for the green condition.
Order Matters: Ensure the red condition is checked first in the Conditional Formatting Rules Manager (Home -> Conditional Formatting -> Manage Rules). This is important because if both conditions are met, the first one will apply.

[#4]

What if I want to say a text can be anything but F1IL? How would I alter the formula for that?

[#5]

@AmberTafolla 

Use $H2<>"F1IL", as shown in the second rule in my previous reply.

[#6]

If it's not to much trouble, could you explain how this formula works please? It's not formatting the way I am hoping and I think it's because of me. I would really appreciate an explanation so I can manipulate the formula correctly for the rest of my spreadsheet

[#7]

@AmberTafolla 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#8]

@HansVogelaar 

Ok. I have attached a clean workbook. I added the prompts that I am needing a formula for so the cells can format properly. 

[#9]

@AmberTafolla 

Thanks. Select A2:G19 and clear the existing rules.

Then select A2:A19.

Hold down Ctrl and select C2:C19, then E2:E19 and finally G2:G19.

The active cell in the selection should be in row 2 (i.e. A2, C2, E2, or G2 - it does not matter which of these).

Set the fill color to red - this will be the default.

We're going to create only one rule.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=AND(OR(AND($A2<>"", $C2<>"F1IL", $E2=""), AND($A2="", $C2="F1IL", $E2<>"")), $G2>0.17)

This formula is a direct translation of the two conditions for cells to be green. In both conditions, G2 (Hours)should be greater than 0.17. In one condition, A2 (Job) should be filled in, C2 (Work Center) should not be F1IL, and G2 (Code) should be blank. In the other condition, these are reversed.

Click Format...
Activate the Fill tab.
Select green as fill color.
Click OK, then click OK again.

See the attached version.

[#10]

I can't have the default be red, or the people entering the information on this spreadsheet won't be notified that they entered something in wrong. That is why I was looking for information on how to create the different formulas.
The spreadsheet needs to start off blank and then populates with color when information is entered right or wrong so that person knows if they need to fix their entries without me having to tell them.
If I know how the formula works I should be able to adjust it for all the different conditions.

[#11]

@AmberTafolla 

As long as the conditions for green are not satisfied, there will be something missing or incorrect, so it's reasonable to color the cells red...

[#12]

Except some of the rows may need to be skipped because they don't need to be filled out at that time in which case they shouldn't be colored in since the information isn't wrong or missing.
If I knew how the formula worked I would be able to adjust it for all the different conditions that I need since this isn't an easy peasy spreadsheet where one format condition will work.

[#13]

@AmberTafolla 

You can easily translate the conditions to formulas. For example, red if

job has number and work center is f1il and code is entered and hours are blank or "0"

translates to

=AND($A2<>"", $C2="F1IL", $E2<>"", $G2=0)

Similarly

job has no number and work center is not f1il or blank and code is blank and hours are greater than .17

translates to

=AND($A2="", $C2<>"F1IL", $E2="", $G2>0.17)