I'm trying to apply formatting to a cell value with the TEXT() based on the value of the cell while also having the cell background and text color change with conditional formatting. The issue I'm running into is that it seems that the TEXT() overrides any conditional formatting that would be applied.
This is the function I'm using in the cell:
=IF(Sheet2!A10>999, TEXT(Sheet2!A10, "#,###"), TEXT(Sheet2!A10, "###.##")
It achieves what I'm wanting it to do which is to display a number with a comma and no decimal if the value is greater than 999. If the value is less than 999, it displays a number with a decimal point and two digits to the right of the decimal.
I also have conditional formatting applied to the cell to change the background and text color if the value in the cell is greater than 2,000. The conditional formatting to change the background and text color does not work.
Is there a way to have all of this worked into the conditional formatting? When I try and create a function using custom function under conditional formatting, it doesn't seem to work when referencing a value from a different sheet. Any help with this would be greatly appreciated.
I believe you can achieve your desired result without any formulas at all by using 'meta instructions' in a custom number format. Apply the following custom number format to your number cells:
[<1000]###.##;[>1000]#,###
You can then use conditional formatting to colour cells >2000 in the standard way because you no longer need to coerce numerical values to text using a formula in order to achieve the desired format.