Conditional formatting after concatenate fails

Question

I'm using the function concatenate to show two values in one cell (the sheet shows the amount present ("X") and the amount required ("Y"), presenting a concatenated value of X / Y). X is calculated and Y is a fixed value in another sheet.

I want to make two events visible by using conditional formatting: when X is equal to Y (background color needs to turn green) and when X is higher than Y (background color needs to turn red). Values range between 0 and 99.

The current conditional format is strictly text based "1 / 1" makes it green, "2 / 1" makes it red and so on which is very ineffective.

Is there a way that I can use one conditional rule per event? I've been struggling with rules per cell (made a comparison between the calculated value and the fixed value); but this meant I had to write this formula for every calculation. With over 300 calculated cells this was a pain to do.

I bet there's some smart solution I'm missing :-)

Answer 1

Perhaps a rule based on a formula something like the following for the "green" condition:

=--LEFT(A1,FIND(" /",A1))=--MID(A1,FIND("/ ",A1)+LEN("/ "),99)

and a similar one for the "red" condition, changing the middle = to >.