Format Conversions

  • Thread starter Thread starter Todd Huttenstine
  • Start date Start date
T

Todd Huttenstine

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



.
Click to expand...
 
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
 
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
 
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





.
Click to expand...
 
Back
Top