Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format cell based on function result
Would like to format conditionally using VB. I know how to use "conditional
format" on a cell in the spreadsheet, but want to do this in the code. In particular, I have thousands of rows of "events". Content of first two cells in each are strings representing time. The function "get_diff" returns difference, in seconds. So cell A3 contains "='PERSONAL.xls'!Module1.get_diff(A1,A2)" Function get_diff(str0 As String, str1 As String, Optional dbg_flag As Boolean = False) As Double Dim xDiff As Double xDiff = convert_to_sec(str1, dbg_flag) - convert_to_sec(str0, dbg_flag) If dbg_flag Then MsgBox "Diff is " & Format(xDiff, "#######.######") End If get_diff = xDiff End Function How do I format the cell that gets the result based on value returned from get_diff? That is the A3 (or any other cell from which I called get_diff) gets formatted based on the result. I can't figure out how to select the cell in the code. I need something like: If xDiff 3.001 Then .....Font.Bold = True .....Font.ColorIndex = 3 Else .....Font.Bold = False .....Font.ColorIndex = 10 Endif |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format cell based on function result
Functions (UDF) called from within a sheet return values... Period. They can
not modify formats or change the values of cells that they are not in. If the function is called in code then it can do whatever it wants but not called from a cell. Your formatting will have to be done externally via conditional formats or some other means... Assuming that I understood your question correctly... -- HTH... Jim Thomlinson "laura_in_abq" wrote: Would like to format conditionally using VB. I know how to use "conditional format" on a cell in the spreadsheet, but want to do this in the code. In particular, I have thousands of rows of "events". Content of first two cells in each are strings representing time. The function "get_diff" returns difference, in seconds. So cell A3 contains "='PERSONAL.xls'!Module1.get_diff(A1,A2)" Function get_diff(str0 As String, str1 As String, Optional dbg_flag As Boolean = False) As Double Dim xDiff As Double xDiff = convert_to_sec(str1, dbg_flag) - convert_to_sec(str0, dbg_flag) If dbg_flag Then MsgBox "Diff is " & Format(xDiff, "#######.######") End If get_diff = xDiff End Function How do I format the cell that gets the result based on value returned from get_diff? That is the A3 (or any other cell from which I called get_diff) gets formatted based on the result. I can't figure out how to select the cell in the code. I need something like: If xDiff 3.001 Then .....Font.Bold = True .....Font.ColorIndex = 3 Else .....Font.Bold = False .....Font.ColorIndex = 10 Endif |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format cell based on function result
Jim,
hmmm, what do you think if I also pass a reference to cell A3 in the UDF? "Jim Thomlinson" wrote: Functions (UDF) called from within a sheet return values... Period. They can not modify formats or change the values of cells that they are not in. If the function is called in code then it can do whatever it wants but not called from a cell. Your formatting will have to be done externally via conditional formats or some other means... Assuming that I understood your question correctly... -- HTH... Jim Thomlinson "laura_in_abq" wrote: Would like to format conditionally using VB. I know how to use "conditional format" on a cell in the spreadsheet, but want to do this in the code. In particular, I have thousands of rows of "events". Content of first two cells in each are strings representing time. The function "get_diff" returns difference, in seconds. So cell A3 contains "='PERSONAL.xls'!Module1.get_diff(A1,A2)" Function get_diff(str0 As String, str1 As String, Optional dbg_flag As Boolean = False) As Double Dim xDiff As Double xDiff = convert_to_sec(str1, dbg_flag) - convert_to_sec(str0, dbg_flag) If dbg_flag Then MsgBox "Diff is " & Format(xDiff, "#######.######") End If get_diff = xDiff End Function How do I format the cell that gets the result based on value returned from get_diff? That is the A3 (or any other cell from which I called get_diff) gets formatted based on the result. I can't figure out how to select the cell in the code. I need something like: If xDiff 3.001 Then .....Font.Bold = True .....Font.ColorIndex = 3 Else .....Font.Bold = False .....Font.ColorIndex = 10 Endif |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format cell based on function result
A UDF can know what cell it is located in something like this
Public Function Tada() As String Tada = Application.Caller.Address End Function In Cell A1 add the formula =tada() That being said even if it knows what cell it is in it can not change the formatting of that cell. There is NO formula that you can type in a cell to change it's format (conditional formatting is different as it is not entered in a cell). Assuming that you do not want to do any kind of math on the return value of the function you can make it text and the text can be formatted however you want... Public Function ThisDate() As String ThisDate = Format(Now(), "Mmm dd") End Function Public Function ThatDate() As String ThatDate = Format(Now(), "dddd mmmm d, yyyy") End Function -- HTH... Jim Thomlinson "laura_in_abq" wrote: Jim, hmmm, what do you think if I also pass a reference to cell A3 in the UDF? "Jim Thomlinson" wrote: Functions (UDF) called from within a sheet return values... Period. They can not modify formats or change the values of cells that they are not in. If the function is called in code then it can do whatever it wants but not called from a cell. Your formatting will have to be done externally via conditional formats or some other means... Assuming that I understood your question correctly... -- HTH... Jim Thomlinson "laura_in_abq" wrote: Would like to format conditionally using VB. I know how to use "conditional format" on a cell in the spreadsheet, but want to do this in the code. In particular, I have thousands of rows of "events". Content of first two cells in each are strings representing time. The function "get_diff" returns difference, in seconds. So cell A3 contains "='PERSONAL.xls'!Module1.get_diff(A1,A2)" Function get_diff(str0 As String, str1 As String, Optional dbg_flag As Boolean = False) As Double Dim xDiff As Double xDiff = convert_to_sec(str1, dbg_flag) - convert_to_sec(str0, dbg_flag) If dbg_flag Then MsgBox "Diff is " & Format(xDiff, "#######.######") End If get_diff = xDiff End Function How do I format the cell that gets the result based on value returned from get_diff? That is the A3 (or any other cell from which I called get_diff) gets formatted based on the result. I can't figure out how to select the cell in the code. I need something like: If xDiff 3.001 Then .....Font.Bold = True .....Font.ColorIndex = 3 Else .....Font.Bold = False .....Font.ColorIndex = 10 Endif |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format based on forumula result | Excel Discussion (Misc queries) | |||
IF function based on True result with large formula. | Excel Programming | |||
Can Excel operate a function based on a true or false result? | Excel Worksheet Functions | |||
format cell based on results of vlookup function | Excel Worksheet Functions | |||
how do I insert a row based on a function result | Excel Worksheet Functions |