| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a cell format based on a function operation
Hi,
I am trying to setup a basic invoice sheet and I have started a function that will calculate the number of days owing based on the current date and the date of the invoice.....it sets a value as per below for a cell in the invoice row Function strDebtorDays(intDebtorDays As Integer) As String 'This function will return a string value showing the number of days that the debt has been owed Select Case intDebtorDays Case 0 To 7 strDebtorDays = "< 7 days" Case 8 To 14 strDebtorDays = "< 14 days" Case 15 To 30 strDebtorDays = "< 30 days" Case 31 To 60 strDebtorDays = "< 60 days" Case 61 To 90 strDebtorDays = "< 90 days" Case Is 90 strDebtorDays = " 90 days" Case Else strDebtorDays = "Not Valid Range" End Select End Function I now want to color the interior of the cell based on one of those values. But because it is function I cannot work out how to update the cell format ? Any help appreciated Thanks Scott |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Setting a cell format based on a function operation
You would need a cell reference to add the color. I don't know how your
code is written to identify the cell where the value goes, i.e. as a variable, or a specified cells or A1 reference. If you are using select or activate to get to the cell, then ActiveCell.Interior.ColorIndex = ? would work in the case statement. Otherwise, use the actual range/cells reference. "scott56hannah" wrote: Hi, I am trying to setup a basic invoice sheet and I have started a function that will calculate the number of days owing based on the current date and the date of the invoice.....it sets a value as per below for a cell in the invoice row Function strDebtorDays(intDebtorDays As Integer) As String 'This function will return a string value showing the number of days that the debt has been owed Select Case intDebtorDays Case 0 To 7 strDebtorDays = "< 7 days" Case 8 To 14 strDebtorDays = "< 14 days" Case 15 To 30 strDebtorDays = "< 30 days" Case 31 To 60 strDebtorDays = "< 60 days" Case 61 To 90 strDebtorDays = "< 90 days" Case Is 90 strDebtorDays = " 90 days" Case Else strDebtorDays = "Not Valid Range" End Select End Function I now want to color the interior of the cell based on one of those values. But because it is function I cannot work out how to update the cell format ? Any help appreciated Thanks Scott |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Setting a cell format based on a function operation
Hi,
I currently have this function setup on a cell in the worksheet to populate the string (eg "7 days") based on other values in the row.....so when a value changes the cell value is recalculated. At that point I want the format changed based on the value.....so the cell is not really active when the function is recalculated.... Is there anyway around this so that I can still format the cell depending on that value. I also want to be able to format the cell range with more than 3 types of fill color so the conditional formatting will not work Thanks Scott "JLGWhiz" wrote: You would need a cell reference to add the color. I don't know how your code is written to identify the cell where the value goes, i.e. as a variable, or a specified cells or A1 reference. If you are using select or activate to get to the cell, then ActiveCell.Interior.ColorIndex = ? would work in the case statement. Otherwise, use the actual range/cells reference. "scott56hannah" wrote: Hi, I am trying to setup a basic invoice sheet and I have started a function that will calculate the number of days owing based on the current date and the date of the invoice.....it sets a value as per below for a cell in the invoice row Function strDebtorDays(intDebtorDays As Integer) As String 'This function will return a string value showing the number of days that the debt has been owed Select Case intDebtorDays Case 0 To 7 strDebtorDays = "< 7 days" Case 8 To 14 strDebtorDays = "< 14 days" Case 15 To 30 strDebtorDays = "< 30 days" Case 31 To 60 strDebtorDays = "< 60 days" Case 61 To 90 strDebtorDays = "< 90 days" Case Is 90 strDebtorDays = " 90 days" Case Else strDebtorDays = "Not Valid Range" End Select End Function I now want to color the interior of the cell based on one of those values. But because it is function I cannot work out how to update the cell format ? Any help appreciated Thanks Scott |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Setting a cell format based on a function operation
Then you should be able to use the specific cell reference in your case
statement to set the color like so: 'Assume Range{"$C$5") is the cell that strDebtorDays checks. 'Replace the ? with a number 1 - 56. Select Case intDebtorDays Case 0 To 7 strDebtorDays = "< 7 days" Range("$C$5").Interior.ColorIndex = ? Case 8 To 14 strDebtorDays = "< 14 days" Range("$C$5").Interior.ColorIndex = ? Case 15 To 30 strDebtorDays = "< 30 days" Range("$C$5").Interior.ColorIndex = ? Case 31 To 60 strDebtorDays = "< 60 days" Range("$C$5").Interior.ColorIndex = ? Case 61 To 90 strDebtorDays = "< 90 days" Range("$C$5").Interior.ColorIndex = ? Case Is 90 strDebtorDays = " 90 days" Range("$C$5").Interior.ColorIndex = ? Case Else strDebtorDays = "Not Valid Range" End Select End Function "scott56hannah" wrote: Hi, I currently have this function setup on a cell in the worksheet to populate the string (eg "7 days") based on other values in the row.....so when a value changes the cell value is recalculated. At that point I want the format changed based on the value.....so the cell is not really active when the function is recalculated.... Is there anyway around this so that I can still format the cell depending on that value. I also want to be able to format the cell range with more than 3 types of fill color so the conditional formatting will not work Thanks Scott "JLGWhiz" wrote: You would need a cell reference to add the color. I don't know how your code is written to identify the cell where the value goes, i.e. as a variable, or a specified cells or A1 reference. If you are using select or activate to get to the cell, then ActiveCell.Interior.ColorIndex = ? would work in the case statement. Otherwise, use the actual range/cells reference. "scott56hannah" wrote: Hi, I am trying to setup a basic invoice sheet and I have started a function that will calculate the number of days owing based on the current date and the date of the invoice.....it sets a value as per below for a cell in the invoice row Function strDebtorDays(intDebtorDays As Integer) As String 'This function will return a string value showing the number of days that the debt has been owed Select Case intDebtorDays Case 0 To 7 strDebtorDays = "< 7 days" Case 8 To 14 strDebtorDays = "< 14 days" Case 15 To 30 strDebtorDays = "< 30 days" Case 31 To 60 strDebtorDays = "< 60 days" Case 61 To 90 strDebtorDays = "< 90 days" Case Is 90 strDebtorDays = " 90 days" Case Else strDebtorDays = "Not Valid Range" End Select End Function I now want to color the interior of the cell based on one of those values. But because it is function I cannot work out how to update the cell format ? Any help appreciated Thanks Scott |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Setting a cell format based on a function operation
Unfortunately I need to make it repeatable across each row that the detail is
in. But I think I can do that by passing in the cell reference to the function call and then using that to perform the format setting... Thanks for your help.... "JLGWhiz" wrote: Then you should be able to use the specific cell reference in your case statement to set the color like so: 'Assume Range{"$C$5") is the cell that strDebtorDays checks. 'Replace the ? with a number 1 - 56. Select Case intDebtorDays Case 0 To 7 strDebtorDays = "< 7 days" Range("$C$5").Interior.ColorIndex = ? Case 8 To 14 strDebtorDays = "< 14 days" Range("$C$5").Interior.ColorIndex = ? Case 15 To 30 strDebtorDays = "< 30 days" Range("$C$5").Interior.ColorIndex = ? Case 31 To 60 strDebtorDays = "< 60 days" Range("$C$5").Interior.ColorIndex = ? Case 61 To 90 strDebtorDays = "< 90 days" Range("$C$5").Interior.ColorIndex = ? Case Is 90 strDebtorDays = " 90 days" Range("$C$5").Interior.ColorIndex = ? Case Else strDebtorDays = "Not Valid Range" End Select End Function "scott56hannah" wrote: Hi, I currently have this function setup on a cell in the worksheet to populate the string (eg "7 days") based on other values in the row.....so when a value changes the cell value is recalculated. At that point I want the format changed based on the value.....so the cell is not really active when the function is recalculated.... Is there anyway around this so that I can still format the cell depending on that value. I also want to be able to format the cell range with more than 3 types of fill color so the conditional formatting will not work Thanks Scott "JLGWhiz" wrote: You would need a cell reference to add the color. I don't know how your code is written to identify the cell where the value goes, i.e. as a variable, or a specified cells or A1 reference. If you are using select or activate to get to the cell, then ActiveCell.Interior.ColorIndex = ? would work in the case statement. Otherwise, use the actual range/cells reference. "scott56hannah" wrote: Hi, I am trying to setup a basic invoice sheet and I have started a function that will calculate the number of days owing based on the current date and the date of the invoice.....it sets a value as per below for a cell in the invoice row Function strDebtorDays(intDebtorDays As Integer) As String 'This function will return a string value showing the number of days that the debt has been owed Select Case intDebtorDays Case 0 To 7 strDebtorDays = "< 7 days" Case 8 To 14 strDebtorDays = "< 14 days" Case 15 To 30 strDebtorDays = "< 30 days" Case 31 To 60 strDebtorDays = "< 60 days" Case 61 To 90 strDebtorDays = "< 90 days" Case Is 90 strDebtorDays = " 90 days" Case Else strDebtorDays = "Not Valid Range" End Select End Function I now want to color the interior of the cell based on one of those values. But because it is function I cannot work out how to update the cell format ? Any help appreciated Thanks Scott |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dynamically setting CELL COLORS based on TWO OTHER cell values | Excel Discussion (Misc queries) | |||
| Format cell based on function result | Excel Programming | |||
| Setting Cell Number Format With A Worksheet Function | Excel Worksheet Functions | |||
| format cell based on results of vlookup function | Excel Worksheet Functions | |||
| setting value to be based on previous cell value | Excel Programming | |||
Linear Mode
