Excel Conditional Formatting Help

[Reynns]

Hey Gurus,

Will kindly appreciate your expertise as I can't seem to implement conditional formatting for the following situation:

1) Both cells to be highlighted in red when value of column 1 is lesser than or equal to 0.2 and when the value of column 2 is greater than or equal to 0.8.

2) The inverse of situation 1 in which the cells will be coloured blue when value of column 1 is greater than or equal to 0.8 and value of column 2 is lesser than or equal to 0.2

I have tried using COUNTIF(Column1, <=0.2)AND(Column2, >=0.8) but got prompted that there was a problem with this formula and try as I may, i can't seem to find an alternative.

For reference, what I hope to achieve is as per depicted:

Will it also be possible to filter away the non-highlighted cells as well?

Thanks for your help butties!

 

[SiaoAngMoh]

Select the cells you want to format and try this as the conditional formatting formula ...

=AND($B2>=0.8, $C2<=0.2) a set the fill as blue

Add a second rule and use

=AND($B2>=0.2, $C2<=0.8) and set fill as red.

Note, this should match your description, which appears to be the opposite of your example image.

As for hiding rows without the formatting applied, I only see two ways. One is VBA. The other is to set up a third column, put a formula in there like =IF(OR(AND(B2>=0.8, C2<=0.2),AND(B2<=0.2,C2>=0.8)),1,0) and paste it down the table. Then use the autofilter to only show those with 1 in that column

 

[Reynns]

Hey SAM,

Thanks for your help and yes, you're right, the picture which I have shown, is the inverse of what I was describing .

That aside, rule-wise, for the red portion, it should be:
=AND($B2<=0.2, $C2>=0.8)

Still working on the filtering portion since the two columns are actually going to be derived from the average of several other data but really appreciate your help.

Thanks buttie!

Cheers,

Reynns