VBA - CF problem

[Minkowski]

hello everyone i run this code and i get 2 errors
1)in range Test1 value zero is both green and red (some green other red)
2)in range Test2 more than 100 gives red font & less no condition

P.S: i have excel 2007
what am i doing wrong?(if i disable the last condition i get them green)
thanks in advance!

Option Explicit


Private Sub Worksheet_Calculate()

    
    Range("Test1").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="0"
    Selection.FormatConditions(1).Font.Color = -11489280
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, Formula1:="=0"
    Selection.FormatConditions(2).Font.Color = -16776961
  
    
   Range("Test2").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="100"
    Selection.FormatConditions(1).Font.Color = -11489280
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, Formula1:="=100"
    Selection.FormatConditions(2).Font.Color = -16776961

End Sub

 

[sous2817]

Hm, I tested it and don't seem to have any issues:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">-5</td><td style="text-align: right;;"></td><td style="text-align: right;;">90</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">-4</td><td style="text-align: right;;"></td><td style="text-align: right;;">91</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">92</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">-2</td><td style="text-align: right;;"></td><td style="text-align: right;;">93</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">-1</td><td style="text-align: right;;"></td><td style="text-align: right;;">94</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">95</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">96</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">97</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">98</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">99</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">100</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">101</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">102</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;">103</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;">104</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;">105</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

In column A (test1), anything 0 or lower is red and 1 or greater is green. Same for column B (test2). Anything 100 or less is red and greater than 100 is green.

How are your ranges being defined?

 

[Minkowski]

Test1

=Sheet1!$B$28:$C$36,Sheet1!$J$28:$K$36,Sheet1!$R$28:$S$36,Sheet1!$Z$28:$AA$36,Sheet1!$AH$28:$AI$36,Sheet1!$AP$28:$AQ$36

and Test2

=Sheet1!$D$28:$E$35,Sheet1!$L$28:$M$35,Sheet1!$T$28:$U$35,Sheet1!$AB$28:$AC$35,Sheet1!$AJ$28:$AK$35,Sheet1!$AR$28:$AS$35

 

[Minkowski]

ok i just saw that for the 1st 2 conditions it is ok
0 was -0.2
and +0.1 so this is ok....! (sorry)

but still the other ones?
i get red over 100
and nothing less than 100