Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Conditions
Hey guys
On sheet3 I have values in Range A5:A105. This Range contains names of stats. I will have numbers(the raw stat) in the next column (B5:B105). On Sheet4 in range M2:M100 I have stat names. All the stat names on sheet3 in Range A5:A105 will be in range M2:M100 but all the stat names in Range M2:M100 may not be in the list of stats on sheet3 (Range A5:A105). Because of this, I need for a code to go through the range on sheet3 (A5:A105) and look for the matching stat name in the range on sheet4 (B5:B105). When it finds a match I need the code on sheet4 to offset 2 columns to the right and look at the value in the corresponding cell (value in columnO). The value in ColumnO will either say "number", "percent", or "time". This is how I need the cell offset1 to the right on sheet3 to be formatted. For example. The code is run. It first goes through values on sheet3 range A5:A105. The value of cell A5 is Productivity. The code then goes to sheet4 and finds the stat name called Productivity. When it finds it, it then offsets 2 columns over and sees the value is "number" The code must then go back to sheet3 where stat Productivity is located and then offset 1 column over to cell B5 and format cell B5 as General Number. The code must then loop through all the stats in Raange Sheet3 A5:A105 until there are no more stats in the range. Number is to be formatted as General Number Percent is to be formatted as Custom 0.00"%" Time is to be formatted as time. Thanx |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Conditions
Dim cell as Range, res as Variant
for each cell in worksheets("sheet3").Range("A5:A105") res = Application.Vlookup(cell.Value, _ Worksheets("sheet4").Range("M2:O100"),3,False) if not iserror(res) then Select Case lcase(res) Case "number" cell.offset(0,1).NumberFormat = "General" Case "percent" cell.offset(0,1).NumberFormat = "0.00%" Case "time" cell.offset(0,1).Numberformat = "hh:mm:ss" End Select End If Next -- Regards, Tom Ogilvy Todd Huttenstine wrote in message ... Hey guys On sheet3 I have values in Range A5:A105. This Range contains names of stats. I will have numbers(the raw stat) in the next column (B5:B105). On Sheet4 in range M2:M100 I have stat names. All the stat names on sheet3 in Range A5:A105 will be in range M2:M100 but all the stat names in Range M2:M100 may not be in the list of stats on sheet3 (Range A5:A105). Because of this, I need for a code to go through the range on sheet3 (A5:A105) and look for the matching stat name in the range on sheet4 (B5:B105). When it finds a match I need the code on sheet4 to offset 2 columns to the right and look at the value in the corresponding cell (value in columnO). The value in ColumnO will either say "number", "percent", or "time". This is how I need the cell offset1 to the right on sheet3 to be formatted. For example. The code is run. It first goes through values on sheet3 range A5:A105. The value of cell A5 is Productivity. The code then goes to sheet4 and finds the stat name called Productivity. When it finds it, it then offsets 2 columns over and sees the value is "number" The code must then go back to sheet3 where stat Productivity is located and then offset 1 column over to cell B5 and format cell B5 as General Number. The code must then loop through all the stats in Raange Sheet3 A5:A105 until there are no more stats in the range. Number is to be formatted as General Number Percent is to be formatted as Custom 0.00"%" Time is to be formatted as time. Thanx |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Conditions
I had to use = "0.00""%"""
This worked. Thank you. -----Original Message----- Dim cell as Range, res as Variant for each cell in worksheets("sheet3").Range("A5:A105") res = Application.Vlookup(cell.Value, _ Worksheets("sheet4").Range("M2:O100"),3,False) if not iserror(res) then Select Case lcase(res) Case "number" cell.offset(0,1).NumberFormat = "General" Case "percent" cell.offset(0,1).NumberFormat = "0.00%" Case "time" cell.offset(0,1).Numberformat = "hh:mm:ss" End Select End If Next -- Regards, Tom Ogilvy Todd Huttenstine wrote in message ... Hey guys On sheet3 I have values in Range A5:A105. This Range contains names of stats. I will have numbers(the raw stat) in the next column (B5:B105). On Sheet4 in range M2:M100 I have stat names. All the stat names on sheet3 in Range A5:A105 will be in range M2:M100 but all the stat names in Range M2:M100 may not be in the list of stats on sheet3 (Range A5:A105). Because of this, I need for a code to go through the range on sheet3 (A5:A105) and look for the matching stat name in the range on sheet4 (B5:B105). When it finds a match I need the code on sheet4 to offset 2 columns to the right and look at the value in the corresponding cell (value in columnO). The value in ColumnO will either say "number", "percent", or "time". This is how I need the cell offset1 to the right on sheet3 to be formatted. For example. The code is run. It first goes through values on sheet3 range A5:A105. The value of cell A5 is Productivity. The code then goes to sheet4 and finds the stat name called Productivity. When it finds it, it then offsets 2 columns over and sees the value is "number" The code must then go back to sheet3 where stat Productivity is located and then offset 1 column over to cell B5 and format cell B5 as General Number. The code must then loop through all the stats in Raange Sheet3 A5:A105 until there are no more stats in the range. Number is to be formatted as General Number Percent is to be formatted as Custom 0.00"%" Time is to be formatted as time. Thanx . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Conditions
You shouldn't have to:
set cell = ActiveCell cell.offset(0,1).NumberFormat = "0.00%" ? ActiveCell.Numberformat 0.00% ? Cell.Text 32.00% ? cell.value 0.32 I guess it might depend on whether you actually have a percent (decimal Fraction) stored in the cell. -- Regards, Tom Ogilvy Todd Huttenstine wrote in message ... I had to use = "0.00""%""" This worked. Thank you. -----Original Message----- Dim cell as Range, res as Variant for each cell in worksheets("sheet3").Range("A5:A105") res = Application.Vlookup(cell.Value, _ Worksheets("sheet4").Range("M2:O100"),3,False) if not iserror(res) then Select Case lcase(res) Case "number" cell.offset(0,1).NumberFormat = "General" Case "percent" cell.offset(0,1).NumberFormat = "0.00%" Case "time" cell.offset(0,1).Numberformat = "hh:mm:ss" End Select End If Next -- Regards, Tom Ogilvy Todd Huttenstine wrote in message ... Hey guys On sheet3 I have values in Range A5:A105. This Range contains names of stats. I will have numbers(the raw stat) in the next column (B5:B105). On Sheet4 in range M2:M100 I have stat names. All the stat names on sheet3 in Range A5:A105 will be in range M2:M100 but all the stat names in Range M2:M100 may not be in the list of stats on sheet3 (Range A5:A105). Because of this, I need for a code to go through the range on sheet3 (A5:A105) and look for the matching stat name in the range on sheet4 (B5:B105). When it finds a match I need the code on sheet4 to offset 2 columns to the right and look at the value in the corresponding cell (value in columnO). The value in ColumnO will either say "number", "percent", or "time". This is how I need the cell offset1 to the right on sheet3 to be formatted. For example. The code is run. It first goes through values on sheet3 range A5:A105. The value of cell A5 is Productivity. The code then goes to sheet4 and finds the stat name called Productivity. When it finds it, it then offsets 2 columns over and sees the value is "number" The code must then go back to sheet3 where stat Productivity is located and then offset 1 column over to cell B5 and format cell B5 as General Number. The code must then loop through all the stats in Raange Sheet3 A5:A105 until there are no more stats in the range. Number is to be formatted as General Number Percent is to be formatted as Custom 0.00"%" Time is to be formatted as time. Thanx . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional format with 3 conditions | Excel Worksheet Functions | |||
Conditional Format - 3 conditions | Excel Worksheet Functions | |||
Two conditions for same format?? | Excel Discussion (Misc queries) | |||
more conditional format conditions | Excel Worksheet Functions | |||
Removing Certain Format Conditions | Excel Programming |