Forum Discussion
Conditional Formatting with Multiple Cell Values
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...
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
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?
Ok. I have attached a clean workbook. I added the prompts that I am needing a formula for so the cells can format properly.
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.
- 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.
