conditional formatting not working properly

[raffexpat]

I have used conditional formatting on an entire column to check a formula. The formula used is "if format is=W9=(P9-E9-Q9)" then format font red. It works for some cells but not others. When I check the same result using =if(W9=(P9-E9-Q9),"OK","Error". It does the same thing. I shows OK for some and notfor others. If I then check the results manually I get the right results for the entire column and it all checks out. With the first option I highlighted all the cells but removed absolute references and for the second method I copied and pasted.

 

[raffexpat]

When I double click I get error message 91 and the following text.

object variable or with block variable not set is the error message i get when trying to open html maker.

I will try and reinstall if it is as easy as you say. thanks

 

[Stormseed]

I don't really know why you get the error. But then you could use Excel Jeanie which sbeatton posted earlier in this thread:

Here I am pasting the link again:
http://www.excel-jeanie-html.de/index.php?f=1

Edit:
Besides, getting the error message, I guess you should check incase you have that "HTML" option in the Excel Menubar.

 

[raffexpat]

Hi I am getting there. with a little messing around I now have the add in installed and now need to post it on to the board. I am looking for the insert button and will figure it out soon

 

[raffexpat]

formatted sales.xls
	W	X	Y	Z	AA
8	Option2	Option3		ManualCheck
9	33,677.0833333	19,570.8333333	OK	33,677.083333
10	30,941.6666667	20,276.6666667	NOT	30,941.666667
11	26,763.8888889	20,105.5555556	NOT	26,763.888889
12	36,075.0000000	30,030.0000000	NOT	36,075.000000
13	29,831.2500000	24,832.5000000	OK
14	30,941.6666667	20,276.6666667	NOT
15	25,909.7222222	19,463.8888889	NOT
16	35,625.0000000	19,950.0000000	OK
17	30,158.3333333	19,763.3333333	OK
18	29,375.0000000	19,250.0000000	OK
19	31,912.5000000	26,565.0000000	NOT
20	36,768.7500000	30,607.5000000	NOT
21	31,875.0000000	19,250.0000000	OK
22	28,443.7500000	23,677.5000000	OK
Sheet1 (2)

 

[raffexpat]

formatted sales.xls
	W	X	Y	Z	AA
8	Option2	Option3		ManualCheck
9	33,677.0833333	19,570.8333333	OK	33,677.083333
10	30,941.6666667	20,276.6666667	NOT	30,941.666667
11	26,763.8888889	20,105.5555556	NOT	26,763.888889
12	36,075.0000000	30,030.0000000	NOT	36,075.000000
13	29,831.2500000	24,832.5000000	OK
14	30,941.6666667	20,276.6666667	NOT
15	25,909.7222222	19,463.8888889	NOT
16	35,625.0000000	19,950.0000000	OK
17	30,158.3333333	19,763.3333333	OK
18	29,375.0000000	19,250.0000000	OK
19	31,912.5000000	26,565.0000000	NOT
20	36,768.7500000	30,607.5000000	NOT
21	31,875.0000000	19,250.0000000	OK
22	28,443.7500000	23,677.5000000	OK
Sheet1 (2)

I guess I have now managed to post the portion of the spreadsheet. does this make sense? Do I get a special certificate for this?

 

[raffexpat]

Guys, I am sorry for messing up this thread a little with my experimentation. I ought to explain. It is the column headed option two which I originally wanted to check. with the conditional formatting. If you look at the column headed manual check you will see the results are identical yet the IF formula shows some OK and some NOT. I am confused

 

[Stormseed]

there is no insert button in HTML Maker. You just need to click on the option "Convert sheet to HTML" after you select your data table which you want to show on the Board ! This includes all the formulae you have in your data table. That add-in will then create an HTML webpage with your data and show you a preview of the same. That HTML preview page will have another button titled "Please click this button to send the source to clipboard" and another button called "view source". Once you click on it (do not click the View Source button), you will save that entire HTML code of the preview page in the computer's memory. When you are in the message box of the board, just press Ctrl+V and the entire code is pasted in the message box. Do not edit the code or else you will have errors displaying your data table on the Board.

I hope this helps you.

 

[Stormseed]

oh ok ! you are succeded

 

[raffexpat]

Ok I tried that and posted the proof. thanks for the suggestion

 

[konew1]

I still think the numbers are not identical.
Using your sheet posted above
Try (w10-z10)*100000000000000000000000000
Does that return zero?
With conditional formatting (or anything for that matter) a very small decimal is not zero.
Do you actually care if the difference is 0.000000000001 ?
If not then try to modify the conditional formatting

Change =W9=(P9-E9-Q9)" to =ABS(w9-P9+E9+Q9) < 0.0001 (change the 0.0001 to the level of accuracy that you care about), or else use the ROUND function in the conditional formatting (not in your actual data)