Results 1 to 4 of 4

VBA - CF problem

This is a discussion on VBA - CF problem within the Excel Questions forums, part of the Question Forums category; hello everyone i run this code and i get 2 errors 1)in range Test1 value zero is both green and ...

  1. #1
    Board Regular
    Join Date
    Sep 2009
    Posts
    157

    Default VBA - CF problem

    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

  2. #2
    Board Regular sous2817's Avatar
    Join Date
    Feb 2008
    Location
    Raleigh, NC
    Posts
    2,260

    Default Re: VBA - CF problem

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

    Excel 2007
    ABC
    1-590
    2-491
    3092
    4-293
    5-194
    6095
    7196
    8297
    9098
    10499
    115100
    120101
    130102
    148103
    159104
    1610105

    Sheet1





    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 by sous2817; May 9th, 2011 at 01:15 PM.
    "Even a blind pig finds an acorn now and again"

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    - Search Mr. Excel using Google

    - Search Mr. Excel using Bing

  3. #3
    Board Regular
    Join Date
    Sep 2009
    Posts
    157

    Default Re: VBA - CF problem

    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

  4. #4
    Board Regular
    Join Date
    Sep 2009
    Posts
    157

    Default Re: VBA - CF problem

    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com