+ Reply to Thread
Results 1 to 11 of 11

Conditional Formatting to VBA

  1. #1
    Registered User
    Join Date
    01-24-2017
    Location
    home
    MS-Off Ver
    MS Office 2016
    Posts
    31

    Conditional Formatting to VBA

    So I have the code below working that if the cells value in column K is less than Column E *.10 then the cell in column K turns red.

    It is working for every line except line 31, cell K is equal to cell E but it is still making it red, I can't figure out why.

    Sub setCondFormat()
    Range("K13:K42").Select
    With Range("K13:K42")
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=IF($K13="""",FALSE,IF($K13 < $E13*.1,TRUE,FALSE))"
    With .FormatConditions(.FormatConditions.Count)
    .SetFirstPriority
    With .Interior
    .PatternColorIndex = xlAutomatic
    .Color = 255
    .TintAndShade = 0
    End With
    End With
    End With
    End Sub

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,025

    Re: Conditional Formatting to VBA

    Look at the actual numbers - probably K31 is less than E31*.1
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    01-24-2017
    Location
    home
    MS-Off Ver
    MS Office 2016
    Posts
    31

    Re: Conditional Formatting to VBA

    Look at the 2nd line, unless my mental math is just wrong.
    Capture.JPG

  4. #4
    Registered User
    Join Date
    08-07-2015
    Location
    Washington
    MS-Off Ver
    365, v1808 32bit
    Posts
    50

    Re: Conditional Formatting to VBA

    Are the numbers in Row K rounded? If I type 949.70 its not red. If I type 949.71 or higher, it's not red. If I type 949.699, it displays 649.70 in Red.

  5. #5
    Registered User
    Join Date
    01-24-2017
    Location
    home
    MS-Off Ver
    MS Office 2016
    Posts
    31

    Re: Conditional Formatting to VBA

    It is pulling from the formula =E31-I31-J31

    Where E is the sum of two numbers, no more than 2 decimal palces. I is 0.9*F, which is currently 0. And J is G*.9, which comes to exactly 8,547.30. So no rounding should be taking place.

  6. #6
    Registered User
    Join Date
    08-07-2015
    Location
    Washington
    MS-Off Ver
    365, v1808 32bit
    Posts
    50

    Re: Conditional Formatting to VBA

    When working with percentages, there's always a rounding factor. keep expanding the decimals in K31, and you'll eventually see it change down to 949.699999999999. (Even though you have it set to 2 decimals)

  7. #7
    Registered User
    Join Date
    01-24-2017
    Location
    home
    MS-Off Ver
    MS Office 2016
    Posts
    31

    Re: Conditional Formatting to VBA

    Well then, is there anyway around this?

  8. #8
    Registered User
    Join Date
    08-07-2015
    Location
    Washington
    MS-Off Ver
    365, v1808 32bit
    Posts
    50

    Re: Conditional Formatting to VBA

    Sadly, I can't come up with anything at this moment. I was thinking if you could somehow have the data in J pasted as a rounded value, it may help, but what's funny is if I manually type in these exact values:
    9497.00 (in column E) and 8547.30 in column J, and set K= E minus J, I get 949.7000000000001. Not sure where that little 1 is coming from. All cells are formatted as numbers.

  9. #9
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,025

    Re: Conditional Formatting to VBA

    Maybe something like:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    01-24-2017
    Location
    home
    MS-Off Ver
    MS Office 2016
    Posts
    31

    Re: Conditional Formatting to VBA

    Quote Originally Posted by xladept View Post
    Maybe something like:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Seems to work, wondering if it could come back to bite me though
    Last edited by kasermap; 11-02-2018 at 03:25 PM.

  11. #11
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,025

    Re: Conditional Formatting to VBA

    You could change the offset from -.001 to -.005 to account for rounding? Thanks for the rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1