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

Re: Conditional Formatting to VBA

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

1 Attachment(s)

Re: Conditional Formatting to VBA

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

Attachment 596508

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.

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.

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)

Re: Conditional Formatting to VBA

Well then, is there anyway around this?

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.

Re: Conditional Formatting to VBA

Maybe something like:

Formula:

"=IF($K13="""",FALSE,IF($K13 < $E13*.1 -.001,TRUE,FALSE))"

Re: Conditional Formatting to VBA

Quote:

Originally Posted by

**xladept**
Maybe something like:

Formula:

"=IF($K13="""",FALSE,IF($K13 < $E13*.1 -.001,TRUE,FALSE))"

Seems to work, wondering if it could come back to bite me though

Re: Conditional Formatting to VBA

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