Change color when value higher but not equal to

[justinwb]

I am trying to set up a conditional formatting with below formula

Please Login or Register  to view this content.

in Sheet Planning Column Q conditional formatting is set up and is not working

in Sample Planning sheet Column Q I have converted the cells to values then converted with Text to Columns and then to General and the conditional formatting works
but unable to do this as I need all the cross references and formulas to stay

I am not sure what I an doing wrong as I an able to get the code to work but only when the formulas are not there

when number in Column O is greater than but not the same as column Q the cell in Column Q will change to green
I have also added in a Cell contains a blank Value to not change to green

Any help would be great



JustinwB

[AliGW]

Try this:

=AND(O3>VALUE(Q3), $Q3<>"")

[justinwb]

Good afternoon AliGW

Thank you for your help
I was not sure what was causing the problem adding in VALUE as above worked great

JustinwB

[AliGW]

The problem is that column Q contains text, so you have to turn it into a number using VALUE, otherwise Excel cannot compute.

Glad to have helped.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[davesexcel]

You have an &"" that does not seem to belong in your formula.
---
2022-07-16_5-36-49.jpg

[justinwb]

Good afternoon davesexcel

I have added &"" to the end of the formula as this runs down the page by 200 cells
I did not want 0 to be shown when no value was in the other cell

The extra &"" must of been placed there by mistake in the other column as it did not show an error
I have not removed it yet

I just checked &"" was added before I added =IFERROR at the start which then made &"" not needed

Thanks for your help

JustinwB