I thought this would've been one of the simplest ways to use conditional formatting, but apparently not.

To simplify I have two cells, B5, which derives it's value of 858 from a formula; and D2 which derives it's value of 712 from another sheet (which pulls the data from an API).

I've added conditional formatting to B5, that says "When the value of B5 is greater than the value of D2, format the cell background to green."

Or at least that's what I thought the conditional formatting rule said...

Here is a SS of the formatting rule: https://i.imgur.com/NCpl4o6.png https://i.imgur.com/sJc5Fli.png

I've found several sources online stating to use a custom formula '=B5>D2', but this does not work either. https://i.imgur.com/hBvQ7cm.png

Does anyone understand why this isn't working? and what the formatting rule I've written is actually saying?

Edit:

In case it's of any use the explicit value of B5 is '=C2*0.6'; and the explicit value of D2 is '=JSON!B2'.

If the solution involves manipulating the '*0.6' from B5, that value changes over the range I eventually want to apply the conditional formatting to.

CodePudding user response：

With CF rules, everything but "Custom formula" requires literals. That is why your formulas don't work (i.e., formulas aren't literal values).

When using custom formulas, they are relative to the key cell. In your case, that is B5. So I would expect your custom formula =B5>D2 to work.

Unless...

... your D2 value is coming in formatted as a string. In that case, try the following custom CF formula:

=B5>D2*1

The addition of *1 should be enough to convert a string to a value within the formula.