CODEWITHSUNDEEP
excelif-statementexcel-formulaconditional-formattinglogical-operators
user3287723
user3287723

Reputation: 23

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.

Upvotes: 0

Views: 247

Answers (1)

ZygD
ZygD

Reputation: 24386

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

Upvotes: 1

Related Questions