Link to home
Start Free TrialLog in
Avatar of Bryce Bassett
Bryce BassettFlag for United States of America

asked on

Excel conditional formatting added programmatically with VBA displays 15 decimal places?

In Excel 2016, I'm using VBA to add conditional formatting to certain cells, based on a lookup of target values.  Here's my code:

    thistarget = targets(whichyear, whichquarter, lookup)
    Set myrange = ActiveSheet.Cells(lookup, 4)
    myrange.FormatConditions.Delete
    With myrange
        'if less than or equal to target, color green
        .FormatConditions.Add xlCellValue, xlLessEqual, thistarget
        With .FormatConditions(1)
            .Interior.Color = RGB(146, 208, 80)
            .StopIfTrue = True
        End With
        'if greater than target up to 15% over, color amber
        .FormatConditions.Add xlCellValue, xlBetween, thistarget, (thistarget + 0.15)
        With .FormatConditions(2)
            .Interior.Color = RGB(255, 192, 0)
            .StopIfTrue = True
        End With
        'if more than 15% over target, color red
        .FormatConditions.Add xlCellValue, xlGreater, (thistarget + 0.15)
        With .FormatConditions(3)
            .Interior.Color = vbRed
            .StopIfTrue = True
        End With
    End With

Open in new window

Note that thistarget is dimensioned as a "single" value.  I can stop the code after line 1 and verify that thistarget = 0.63.   So what I expect to see is this:
User generated imageBut what I actually see is this:
User generated imageFunctionally it works, but if anybody goes to look at the conditional formatting formulas they are going to think I'm nuts.  

Why is it giving me so many numbers after the decimal?  

By the way, if instead of using a variable I hardcode line 1 to say "thistarget = 0.63" then I get 2 digit precision as expected.
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Hi,

Try changing your first line:
thistarget = targets(whichyear, whichquarter, Lookup)

Open in new window

to
thistarget = Format(targets(whichyear, whichquarter, Lookup), "0.00")

Open in new window

Avatar of Bryce Bassett

ASKER

Worth a try, but this does not solve the issue.  I also tried the Round function, but without success.  I still get all those numbers after the decimal.

Any other ideas?
SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Currency format did the trick.  Thanks, both.