Conditional formatting on 3 overlapping values

[wsurfa]

Is it possible to use standard conditional formatting over 3 overlapping values?

I have a range of cells a5:a57. I want try and do the following

If is greater than A1 then format red text
If is greater than preceeding cell (ie A5>A6, A6>A7 etc) then format orange fill
If greater than A1 and greater than preceeding cell then format red text and orange fill.

The values as examples could be

A1=100

A5 = 104
A6 = 105

or A= 99

A5 = 97
A6 = 98

I can use =IF(AND) for the 3rd criteria, and cell value for the first two.

However when used it only applies the first criteria. I assume this is because the ranges being tested overlap.

Can anyone help?

 

[Stormseed]

Hi

Select cell A5 and follow these steps:

1) Go to Conditional Formatting.

2) In Condition 1, select "Formula Is" and then type this formula exactly as shown below:

=AND($A5>$A$1,$A5>$A6)
Format Text as Red and Fill as Orange Only.

3) In Condition 2, select "Cell Value Is" | select "greater than" and type this:

=$A6
Format Fill as Orange Only.

4) In Condition 3, select "Cell Value Is" | select "greater than" and type this:

=$A$1
Format Text as Red Only.

Finally, select A5 again, press Ctrl+C --> again select range A6:A57 --> Edit --> Paste Special --> Formats --> Click on OK.

 

[wsurfa]

Thanks Stormseed - works perfectly.

I didn't realise the importance of the order, but it's logical.