# Conditional Formatting to VBA

1. ## 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")
"=IF(\$K13="""",FALSE,IF(\$K13 < \$E13*.1,TRUE,FALSE))"
With .FormatConditions(.FormatConditions.Count)
.SetFirstPriority
With .Interior
.PatternColorIndex = xlAutomatic
.Color = 255
End With
End With
End With
End Sub

2. ## Re: Conditional Formatting to VBA

Look at the actual numbers - probably K31 is less than E31*.1

3. ## Re: Conditional Formatting to VBA

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

4. ## 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. ## 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. ## 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. ## Re: Conditional Formatting to VBA

Well then, is there anyway around this?

8. ## 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. ## Re: Conditional Formatting to VBA

Maybe something like:

Formula:
`Please Login or Register  to view this content.`

10. ## Re: Conditional Formatting to VBA

Maybe something like:

Formula:
`Please Login or Register  to view this content.`
Seems to work, wondering if it could come back to bite me though

11. ## Re: Conditional Formatting to VBA

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

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

#### 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