Format Conversions
 

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

Format Conversions
 

Try:

If cellr.NumberFormat = "0.00%" Then
cellr.Value = cellr.value * 100
End If

And after the rest of the code, just undo what you did:

If cellr.NumberFormat = "0.00%" Then
cellr.Value = cellr.value / 100
End If

It's ugly, but it should work.



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

Format Conversions
 

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

Format Conversions
 

Im not sure how to do that.


-----Original Message-----
Try:

If cellr.NumberFormat = "0.00%" Then
cellr.Value = cellr.value * 100
End If

And after the rest of the code, just undo what you did:

If cellr.NumberFormat = "0.00%" Then
cellr.Value = cellr.value / 100
End If

It's ugly, but it should work.



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

.

Format Conversions
 

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

Format Conversions
 

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

Format Conversions
 

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


.