Conditional Formatting using or if else statement to highlight cell either in green or red color

0
votes

I am very new to conditional formatting, all I tried is to Highlighted Cell/column "AN" in Red if the target is more i.e. other cell values is greater than the target. And if the target is within range then Cell AN should be in green colour.

1
excelconditional-formatting

1 Answers

2
votes

All you really need to do is to select the range e.g. AN2:AN4 and pre-fill it unconditionally with a green background just using the formatting section on the Home tab:

enter image description here

Then in the case of any cells where your formula applies, the conditional formatting (red) will over-ride the unconditional formatting (green).

Alternatively if you preferred to do it with conditional formatting you would probably want to invert the logic of your formula and create a separate rule:

=AND($AC2<=$AB2,$AE2<=$AD2,$AG2<=$AF2,$AI2<=$AH2)

specifying fill colour green. Because only one of these rules can be true in any given row, it doesn't matter what order they are in.

Yet another possibility is that you test whether there is any data in the current row and colour it green if there is:

=COUNT(AB2:AI2)>0

in which case the red rule has to come first.

enter image description here