Page 2 of 2 FirstFirst 12
Results 16 to 19 of 19
  1. #16
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB to Return the Absolute Value (Excel 97 SR2 VB )

    Legare,

    How would I change this to an absolute value expression:

    ' AR Over Short Conditional Formatting (Yellow) oCell.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ Formula1:="100.01", Formula2:="200.00" oCell.FormatConditions(1).Interior.ColorIndex = 6

    I need to test for the absolute values between 100.01 but less than 200. I tried :

    oCell.FormatConditions.Add Type:=xlExpression, _
    Formula1:="=Abs(" & oCell.Address & ")>200.01"
    'Formula2:="=Abs(" & oCell.Address & ")<= 300.00"

    This worked but it would not look at the data with both conditions evaluated at the same time like it did when I used xlBetween. Any suggestions?

    Thank you.

  2. #17
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB to Return the Absolute Value (Excel 97 SR2 VB )

    Any ideas why this won't work?

    ' Cash Over Short Condtional Formatting (Yellow)
    oCell.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
    Formula1:="=Abs(" & oCell.Address & ")>200.01", _
    Formula2:="=Abs(" & oCell.Address & ")<= 300.00"
    oCell.FormatConditions(1).Interior.ColorIndex = 6

  3. #18
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,873
    Thanks
    0
    Thanked 79 Times in 75 Posts

    Re: VB to Return the Absolute Value (Excel 97 SR2 VB )

    Hi,
    You could try:
    With oCell
    .FormatConditions.Add Type:=xlExpression, _
    Formula1:="=and(Abs(" & .Address & ")>200.01,abs(" & .Address & ")<=300)"
    .FormatConditions(1).Interior.ColorIndex = 6
    End With
    Hope that helps.
    Regards,
    Rory
    Microsoft MVP - Excel.

  4. #19
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB to Return the Absolute Value (Excel 97 SR2 VB )

    Thanks Rory!! Do you know how long I've been looking for a way to test for an amount between two points with a formula. It's easy to do via conditional formatting but to simulate that with an expression has been frustrating to say the least.

    It works like a charm!! Thanks again.

Page 2 of 2 FirstFirst 12

Posting Permissions

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