VBA - CF problem

Minkowski

Board Regular
Joined
Sep 16, 2009
Messages
157
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!

Code:
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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?
 
Last edited:
Upvote 0
Test1
Code:
=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
Code:
=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
 
Upvote 0
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 :(
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top