Search code examples
excelif-statementexcel-formulaconditional-formattinglogical-operators

Highlight the particular cell if matches the formula in Excel

I have entered following formula in Excel. How to highlight particular cell/cells that fulfills the condition? Here the highlight column is D, E, and F. While X column relates to the condition requirement.

=IF(
    OR(
        AND(D5>X12,E5>X29),
        AND(D5>X12,F5>X45),
        AND(E5>X29,F5>X45),
        AND(D5<X16,E5<X33),
        AND(D5<X16,F5<X49),
        AND(E5<X33,F5<X49)),
    "YES",
    "NO")

The "YES" or "NO would be in a particular column e.g. in I column. But I want to highlight which cell is matching the condition e.g. D5>X12, E5>X29 both if match the condition then both D5 and E5 should be highlighted as Red color. If only one matches the condition then it should not highlight any.

Solution

  • Your grand formula can be rewritten:

    =IF(
     OR(SUM(D5>X12,E5>X29,F5>X45)>1,SUM(D5<X16,E5<X33,F5<X49)>1),
     "YES",
     "NO")

    You will have to put 3 different formulas on 3 different cells or columns.

    Home -> Conditional Formatting -> Manage Rules... ->

    New Rule... -> Choose "Use a formula to determine which cells to format"
    Paste the formula: =OR(AND(SUM($D5<$X16,$E5<$X33,$F5<$X49)>1,$D5<$X16),AND(SUM($D5>$X12,$E5>$X29,$F5>$X45)>1,$D5>$X12))

    Click "Format..." and choose the format.
    OK -> Applies to =$D$5
    Apply

    New Rule... -> Choose "Use a formula to determine which cells to format"
    Paste the formula: =OR(AND(SUM($D5<$X16,$E5<$X33,$F5<$X49)>1,$E5<$X33),AND(SUM($D5>$X12,$E5>$X29,$F5>$X45)>1,$E5>$X29))

    Click "Format..." and choose the format.
    OK -> Applies to =$E$5
    Apply

    New Rule... -> Choose "Use a formula to determine which cells to format"
    Paste the formula: =OR(AND(SUM($D5<$X16,$E5<$X33,$F5<$X49)>1,$F5<$X49),AND(SUM($D5>$X12,$E5>$X29,$F5>$X45)>1,$F5>$X45))

    Click "Format..." and choose the format.
    OK -> Applies to =$F$5
    Apply

    OK