0

I have two sets of data (21x14 sets of cells), on different sheets (but in corresponding cells therein).

Conditional formatting within the sheet works (e.g. Cell Value > 0, applies to =$B$5:$N$25, highlight green), but I want to add a rule that also looks to the other sheet and then formats a different way if the value in the corresponding cell is higher (or lower) than 0 and if the initial cell is lower (or higher) than 0 (e.g. if cell higher than 0, but corresponding cell lower than 0, then highlight yellow. Using the following formula:

=AND($B$5:$N$25>0,OtherSheet!$B$5:$N$25<0)

Applies to: $B$5:$N$25

If relevant - all the cells contain formulae that return a percentage value.

I have also tried doing this to one cell at a time (i.e. not on a 'range' basis) in case that was causing the issue, but the same problem - only the 'native' rules are applied.

Rules are correctly stacked (so rules native to the sheet run first, followed by rule looking at different sheet) so I don't think it is correctly executing it, then overwriting the result.

Any ideas? I am sure I have left out pertinent information, so please ask for any clarifications as are relevant.

Improve this question
1
  • To troubleshoot issues of the order of rules, apply different formatting with different rules eg font colour, background, borders, font style (eg bold). If they all get applied then the cell is matching multiple rules, in which case you need to figure out your conflicts and which rule you want to "win". Consider using "stop if true" to enforce this in a way that makes sense to others using the sheet later.
    – AdamV
    Commented Sep 14, 2023 at 12:32

1 Answer 1

Reset to default
2

The rule for the first cell only, relative addresses:

=AND(B5>0,OtherSheet!B5<0)

Applies to: $B$5:$N$25 as required.
ActiveSheet Other sheet

Improve this answer
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .