About Us

Todd Huttenstine[_2_]

Ah yes that is it. Works thanx

-----Original Message-----
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

.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
conversions	dperalta	Excel Worksheet Functions	1	July 18th 08 10:17 PM
Conversions	dengel	Excel Worksheet Functions	2	February 13th 07 09:56 PM
Odd number conversions???	Christian	Excel Discussion (Misc queries)	3	March 15th 05 09:57 PM
Conversions	Curious	Excel Discussion (Misc queries)	1	February 21st 05 10:14 PM
Conversions	ICMIII	Excel Discussion (Misc queries)	2	February 21st 05 09:19 PM

Menu

About Us