Todd,

Struggling with this one. 93% is .93 so what is wrong?

What values do you have in A5:A100, and in M2:Q100?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Todd Huttenstine" wrote in message
...
below is a code that does conditioanl formatting on cells
using values from cells. It messes up on percents. For
example, if it sees 93% in a cell, the conditioanl format
is uses is .93. This is wrong. I would like for when it
sees a number with a % such as 93%, it put the value of 93
in as the conditional format instead of .93.

How do I do this?



Dim cellr As Range, res As Variant
Worksheets(3).Activate
For Each cellr In Worksheets(3).Range("A5:A100")
res = Application.VLookup(cellr.Value, _
Worksheets(4).Range("M2:Q100"), 4, False)
res1 = Application.VLookup(cellr.Value, _
Worksheets(4).Range("M2:Q100"), 5, False)

If Not IsError(res) Then
cellr.Offset(0, 1).Select
cellr.Offset(0, 1).FormatConditions.Delete
cellr.Offset(0, 1).FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlNotBetween, _
Formula1:=res, Formula2:=res1
cellr.Offset(0, 1).FormatConditions(1). _
Interior.ColorIndex = 3
End If
Next