Dim cellr As Range, res As Variant, res1 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
if instr(cellr.offset(0,1).NumberFormat,"%") then
res = res * 100
res1 = res1 * 100
End if
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

--
Regards,
Tom Ogilvy


Todd Huttenstine wrote in message
...
See this is the code. The cells are already formatted,
this code just adds conditional formatting to the cell
based on information in other cells. The information in
other cells may say 93% and 100%. So the code sets up the
conditional format by saying turn the cell red if the
value within the cell is .93-1. I do not want this.
Eventhough it shows a percent I need the conditional
format to read from 93-100.

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