Forum Discussion

AmberTafolla's avatar
AmberTafolla
Copper Contributor
Aug 16, 2024

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 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!

 

  • 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.

    • AmberTafolla's avatar
      AmberTafolla
      Copper Contributor
      What if I want to say a text can be anything but F1IL? How would I alter the formula for that?
  • Khizar_Hayat's avatar
    Khizar_Hayat
    Brass Contributor
    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.

Share

Resources