Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conversions | Excel Worksheet Functions | |||
Conversions | Excel Worksheet Functions | |||
Odd number conversions??? | Excel Discussion (Misc queries) | |||
Conversions | Excel Discussion (Misc queries) | |||
Conversions | Excel Discussion (Misc queries) |