Conditional formatting with odd behaviour

Armsp0

New Member
Joined
Oct 27, 2017
Messages
2
I require conditional formatting to apply a cell color, either red or green depending on whether it is greater than a critical value contained in an adjacent cell or less than or equal to that same value. The cell to be formatted displays the result of a calculation whose precedents are themselves adjacent in the same row. If the precedents are some values less than 1.000 the formatting works fine. If the precedents are greater than 1.000, the equal to component of the formatting fails to apply the required cell fill color when the calculation result equals the critical value. I have fiddled with this for a while trying the simplest version of the formatting in a stepwise manner, used Excels pre defined rules instead of my formatting formula but to no avail.

The liked file shows two rows illustrating the problem. in both rows the calculated result is equal to the critical value but the formatting is different in each cell.


https://www.dropbox.com/s/5u7xcvo9bzd7kks/condform.xlsm?dl=0
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,386
For some reason excel is actually working out the RED calc as
0.008000000000000010000000000000

and NOT
0.008000000000000000000000000000

I'm not sure why its doing that - the way excel handles number precision or a bug

use round
ROUND(ABS(D7-C7),5)
and then the number of decimals places you are likely to use
I have used 5 here
 

Armsp0

New Member
Joined
Oct 27, 2017
Messages
2
Wow, it did occur to me that there might be an issue with precision but I only checked to a few more places. (and arithmetically shouldn’t have required more precision anyway so far as I could see) Thanks for your thorough investigation Wayne.
 

Forum statistics

Threads
1,081,538
Messages
5,359,396
Members
400,526
Latest member
Brook1083

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top