Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change cell color based on date
Dear Experts,
I would like my users to be able to input a date in a cell, then Excel automatically calculates the elapsed duration in days from the inputted date to today's date, and changes the color of the cell to one of four colors, based on the 3 user-specified durations that correspond to the three colors. So if the user-specified durations are 10 days (if duration d =< 10, cell color = red), 20 (if 10< d =<20, cell color = orange), and 30 (if 20< d =<30, cell color = yellow, and if d30 cell color = green), the cell color will automatically change based on today's date. Ideally, the user-specified thresholds can be different for each cell, or at least for each column. Can this be done with conditional formatting or is code required? I'm new at both so any help would be greatly appreciated! Thank you, RRP333 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change cell color based on date
Conditional Formatting only allows three colors. You use four, so the easy
solution is to set one as a default, then if the dates don't fit the CF this color will show. A sueful alternative is to use the sheet's change event. Place the following code in the sheet's code page....and you can get there quickly by right-clicking th esheet tab & selecting code page. The code is pretty easy to follow... Option Explicit Private Enum eColors orange = 52479 red = 255 green = 65280 yellow = 65535 End Enum Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Range("MyDate").Address Then Select Case Target.Value - Range("controlDate").Value Case Is 30: Target.Interior.Color = eColors.green Case Is 20: Target.Interior.Color = eColors.orange Case Is 10: Target.Interior.Color = eColors.yellow Case Else: Target.Interior.Color = eColors.red End Select End If End Sub "RRP333" wrote: Dear Experts, I would like my users to be able to input a date in a cell, then Excel automatically calculates the elapsed duration in days from the inputted date to today's date, and changes the color of the cell to one of four colors, based on the 3 user-specified durations that correspond to the three colors. So if the user-specified durations are 10 days (if duration d =< 10, cell color = red), 20 (if 10< d =<20, cell color = orange), and 30 (if 20< d =<30, cell color = yellow, and if d30 cell color = green), the cell color will automatically change based on today's date. Ideally, the user-specified thresholds can be different for each cell, or at least for each column. Can this be done with conditional formatting or is code required? I'm new at both so any help would be greatly appreciated! Thank you, RRP333 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change cell color based on date
Hi.
To do the following, set up the following example: In cell B3, type: =NOW() and format it to Date (dd/mm/yyyy) In cell B4, type: 16/01/2005 (this is the user defined date) In cell B5, type: =DAYS360(B4,B3) Now set cell B5's colour to Green (select the cell, right click, Cell Properties, Pattern, and select the Green colour) Now, while still on B5, select Format Conditional Formatting. First row should read "Cell Value Is", "less than or equal to", then type 10. Click Format, and set the pattern to RED. Now select "Add" Second row should read "Cell Value Is", "less than or equal to", then type 20. Click Format, and set the pattern to ORANGE. Now select "Add" Second row should read "Cell Value Is", "less than or equal to", then type 30. Click Format, and set the pattern to YELLOW. This will work, as it excludes the need to look for a 4th condition (a limit in Excel Conditional Formatting). If you wanted RED to be the default colour, change all "less than or equal to" statements to "greater than". The NOW() statement prevents you needing to type in todays date. "RRP333" wrote: Dear Experts, I would like my users to be able to input a date in a cell, then Excel automatically calculates the elapsed duration in days from the inputted date to today's date, and changes the color of the cell to one of four colors, based on the 3 user-specified durations that correspond to the three colors. So if the user-specified durations are 10 days (if duration d =< 10, cell color = red), 20 (if 10< d =<20, cell color = orange), and 30 (if 20< d =<30, cell color = yellow, and if d30 cell color = green), the cell color will automatically change based on today's date. Ideally, the user-specified thresholds can be different for each cell, or at least for each column. Can this be done with conditional formatting or is code required? I'm new at both so any help would be greatly appreciated! Thank you, RRP333 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change cell color based on date
Hi Patrick,
Thank you for the quick reply and code. I entered the code exactly as you wrote in the Sheet 5 (Code) window, and in the code I replaced "My Date" with a cell reference in which a user-specified date appears (cell M8 in this example), and replaced "Control Date" with cell ref E1 in which the fomula =NOW() is entered. Then when I change the date in M8 on Sheet 5, the code window appears with an error box stating: Compile error: Expected: Identifier, and in the code, Enum is highlighted. Hopefully you know what this all means and can please help. If it helps, I can modify the user requirements so if duration is =<10, cell color= no color, then there would be only 3 colors (one for 10 to =<20, one for 20 to =<30, and one for 30). Many cells on the worksheet contain user-specified dates, and the dates will be changed by the user as tasks progress, so I assume that where you show "My Date" in the code, I would enter the range of cells in which user-specified dates appear. Then when any date cell is changed or updated by the user, the code will automatically check all cells in the range and change colors accordingly. Thank you in advance for your help with this debugging - RRP333 "Patrick Molloy" wrote: Conditional Formatting only allows three colors. You use four, so the easy solution is to set one as a default, then if the dates don't fit the CF this color will show. A sueful alternative is to use the sheet's change event. Place the following code in the sheet's code page....and you can get there quickly by right-clicking th esheet tab & selecting code page. The code is pretty easy to follow... Option Explicit Private Enum eColors orange = 52479 red = 255 green = 65280 yellow = 65535 End Enum Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Range("MyDate").Address Then Select Case Target.Value - Range("controlDate").Value Case Is 30: Target.Interior.Color = eColors.green Case Is 20: Target.Interior.Color = eColors.orange Case Is 10: Target.Interior.Color = eColors.yellow Case Else: Target.Interior.Color = eColors.red End Select End If End Sub "RRP333" wrote: Dear Experts, I would like my users to be able to input a date in a cell, then Excel automatically calculates the elapsed duration in days from the inputted date to today's date, and changes the color of the cell to one of four colors, based on the 3 user-specified durations that correspond to the three colors. So if the user-specified durations are 10 days (if duration d =< 10, cell color = red), 20 (if 10< d =<20, cell color = orange), and 30 (if 20< d =<30, cell color = yellow, and if d30 cell color = green), the cell color will automatically change based on today's date. Ideally, the user-specified thresholds can be different for each cell, or at least for each column. Can this be done with conditional formatting or is code required? I'm new at both so any help would be greatly appreciated! Thank you, RRP333 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change cell color based on date
Hi Moo,
Thank you for quick reply. Your solution is nearly what is required. The CF you suggested requires an additional cell (B5) to display the duration (B3-B4) and it changes the color of cell B5, but I need the color of B4 (the user-specified date) to change color. There are many cells on the worksheet in which a user has to enter a date, so if possible it would be preferable to not have to add additional cells to display the duration. If a formula is used in the CF, will this help? Thank you for your help and quick response - RRP333 "Moo" wrote: Hi. To do the following, set up the following example: In cell B3, type: =NOW() and format it to Date (dd/mm/yyyy) In cell B4, type: 16/01/2005 (this is the user defined date) In cell B5, type: =DAYS360(B4,B3) Now set cell B5's colour to Green (select the cell, right click, Cell Properties, Pattern, and select the Green colour) Now, while still on B5, select Format Conditional Formatting. First row should read "Cell Value Is", "less than or equal to", then type 10. Click Format, and set the pattern to RED. Now select "Add" Second row should read "Cell Value Is", "less than or equal to", then type 20. Click Format, and set the pattern to ORANGE. Now select "Add" Second row should read "Cell Value Is", "less than or equal to", then type 30. Click Format, and set the pattern to YELLOW. This will work, as it excludes the need to look for a 4th condition (a limit in Excel Conditional Formatting). If you wanted RED to be the default colour, change all "less than or equal to" statements to "greater than". The NOW() statement prevents you needing to type in todays date. "RRP333" wrote: Dear Experts, I would like my users to be able to input a date in a cell, then Excel automatically calculates the elapsed duration in days from the inputted date to today's date, and changes the color of the cell to one of four colors, based on the 3 user-specified durations that correspond to the three colors. So if the user-specified durations are 10 days (if duration d =< 10, cell color = red), 20 (if 10< d =<20, cell color = orange), and 30 (if 20< d =<30, cell color = yellow, and if d30 cell color = green), the cell color will automatically change based on today's date. Ideally, the user-specified thresholds can be different for each cell, or at least for each column. Can this be done with conditional formatting or is code required? I'm new at both so any help would be greatly appreciated! Thank you, RRP333 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change cell color based on date
I think you've been given bad advice. As you suspect, you don't need an extra
column for this, you can use a formula in the conditional formatting, AND you definitely don't want to use the DAYS360 function to calculate the difference between two dates. You get the number of days between 2 dates by simple subtration. The DAYS360 function is used by bond traders to get around the problems introduced by months of different lengths. For example, it would tell you that the difference between Jan 15 and Feb 15 is 30 days, and the difference between Feb 15 and March 15 is also 30 days. I assume, since you are interested in differences of 10 and 20 days, that you want actual differences, which are 31 and 28 days, respectively. Assuming the date in B4 is a future date. You need 3 conditional formatting formulas, applied in this order =B4-TODAY()30 =B4-TODAY()20 =B4-TODAY()10 On Tue, 1 Mar 2005 18:07:04 -0800, RRP333 wrote: Hi Moo, Thank you for quick reply. Your solution is nearly what is required. The CF you suggested requires an additional cell (B5) to display the duration (B3-B4) and it changes the color of cell B5, but I need the color of B4 (the user-specified date) to change color. There are many cells on the worksheet in which a user has to enter a date, so if possible it would be preferable to not have to add additional cells to display the duration. If a formula is used in the CF, will this help? Thank you for your help and quick response - RRP333 "Moo" wrote: Hi. To do the following, set up the following example: In cell B3, type: =NOW() and format it to Date (dd/mm/yyyy) In cell B4, type: 16/01/2005 (this is the user defined date) In cell B5, type: =DAYS360(B4,B3) Now set cell B5's colour to Green (select the cell, right click, Cell Properties, Pattern, and select the Green colour) Now, while still on B5, select Format Conditional Formatting. First row should read "Cell Value Is", "less than or equal to", then type 10. Click Format, and set the pattern to RED. Now select "Add" Second row should read "Cell Value Is", "less than or equal to", then type 20. Click Format, and set the pattern to ORANGE. Now select "Add" Second row should read "Cell Value Is", "less than or equal to", then type 30. Click Format, and set the pattern to YELLOW. This will work, as it excludes the need to look for a 4th condition (a limit in Excel Conditional Formatting). If you wanted RED to be the default colour, change all "less than or equal to" statements to "greater than". The NOW() statement prevents you needing to type in todays date. "RRP333" wrote: Dear Experts, I would like my users to be able to input a date in a cell, then Excel automatically calculates the elapsed duration in days from the inputted date to today's date, and changes the color of the cell to one of four colors, based on the 3 user-specified durations that correspond to the three colors. So if the user-specified durations are 10 days (if duration d =< 10, cell color = red), 20 (if 10< d =<20, cell color = orange), and 30 (if 20< d =<30, cell color = yellow, and if d30 cell color = green), the cell color will automatically change based on today's date. Ideally, the user-specified thresholds can be different for each cell, or at least for each column. Can this be done with conditional formatting or is code required? I'm new at both so any help would be greatly appreciated! Thank you, RRP333 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change cell color based on date
PS: Another example of the behavior of DAYS360: the number of days from Feb
27, 2005 to March 1, 2005 is 4 rather than 2, since using DAYS360's view of the calendar, Feb has 30 days. On Tue, 01 Mar 2005 20:30:29 -0600, Myrna Larson wrote: I think you've been given bad advice. As you suspect, you don't need an extra column for this, you can use a formula in the conditional formatting, AND you definitely don't want to use the DAYS360 function to calculate the difference between two dates. You get the number of days between 2 dates by simple subtration. The DAYS360 function is used by bond traders to get around the problems introduced by months of different lengths. For example, it would tell you that the difference between Jan 15 and Feb 15 is 30 days, and the difference between Feb 15 and March 15 is also 30 days. I assume, since you are interested in differences of 10 and 20 days, that you want actual differences, which are 31 and 28 days, respectively. Assuming the date in B4 is a future date. You need 3 conditional formatting formulas, applied in this order =B4-TODAY()30 =B4-TODAY()20 =B4-TODAY()10 On Tue, 1 Mar 2005 18:07:04 -0800, RRP333 wrote: Hi Moo, Thank you for quick reply. Your solution is nearly what is required. The CF you suggested requires an additional cell (B5) to display the duration (B3-B4) and it changes the color of cell B5, but I need the color of B4 (the user-specified date) to change color. There are many cells on the worksheet in which a user has to enter a date, so if possible it would be preferable to not have to add additional cells to display the duration. If a formula is used in the CF, will this help? Thank you for your help and quick response - RRP333 "Moo" wrote: Hi. To do the following, set up the following example: In cell B3, type: =NOW() and format it to Date (dd/mm/yyyy) In cell B4, type: 16/01/2005 (this is the user defined date) In cell B5, type: =DAYS360(B4,B3) Now set cell B5's colour to Green (select the cell, right click, Cell Properties, Pattern, and select the Green colour) Now, while still on B5, select Format Conditional Formatting. First row should read "Cell Value Is", "less than or equal to", then type 10. Click Format, and set the pattern to RED. Now select "Add" Second row should read "Cell Value Is", "less than or equal to", then type 20. Click Format, and set the pattern to ORANGE. Now select "Add" Second row should read "Cell Value Is", "less than or equal to", then type 30. Click Format, and set the pattern to YELLOW. This will work, as it excludes the need to look for a 4th condition (a limit in Excel Conditional Formatting). If you wanted RED to be the default colour, change all "less than or equal to" statements to "greater than". The NOW() statement prevents you needing to type in todays date. "RRP333" wrote: Dear Experts, I would like my users to be able to input a date in a cell, then Excel automatically calculates the elapsed duration in days from the inputted date to today's date, and changes the color of the cell to one of four colors, based on the 3 user-specified durations that correspond to the three colors. So if the user-specified durations are 10 days (if duration d =< 10, cell color = red), 20 (if 10< d =<20, cell color = orange), and 30 (if 20< d =<30, cell color = yellow, and if d30 cell color = green), the cell color will automatically change based on today's date. Ideally, the user-specified thresholds can be different for each cell, or at least for each column. Can this be done with conditional formatting or is code required? I'm new at both so any help would be greatly appreciated! Thank you, RRP333 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change cell color based on date
Hi Myrna,
You champion! Thank you so much. Using the CFs you suggested does the trick, especially in the order you suggested. My only modification is the formula is today()-B4, since the user-specified dates are past dates not future dates. Also, rather than "hard-coding" the threshold durations (10, 20, 30), the formula includes a cell ref so a user can easily change the threshold durations at which he wants the colors to change. The worksheet will include cells in which the user can enter the desired threshold durations that are to apply to a column of dates, as the threshold durations will vary from column to column (each column represents a different business task or business group). The only potential hassle I see is that each cell will require setting up the CF. Currently there are about 3000 cells and the worksheet will grow to over 10000 or more cells. Perhaps as the worksheet grows, I wonder if code may be more appropriate rather than entering/maintaining all the CFs. Your explanation of the algorithms behind DAYS 360 is also very good. I now can apply that for other future opportunities. Thank you for your quick reply and solution - RRP333 "Myrna Larson" wrote: I think you've been given bad advice. As you suspect, you don't need an extra column for this, you can use a formula in the conditional formatting, AND you definitely don't want to use the DAYS360 function to calculate the difference between two dates. You get the number of days between 2 dates by simple subtration. The DAYS360 function is used by bond traders to get around the problems introduced by months of different lengths. For example, it would tell you that the difference between Jan 15 and Feb 15 is 30 days, and the difference between Feb 15 and March 15 is also 30 days. I assume, since you are interested in differences of 10 and 20 days, that you want actual differences, which are 31 and 28 days, respectively. Assuming the date in B4 is a future date. You need 3 conditional formatting formulas, applied in this order =B4-TODAY()30 =B4-TODAY()20 =B4-TODAY()10 On Tue, 1 Mar 2005 18:07:04 -0800, RRP333 wrote: Hi Moo, Thank you for quick reply. Your solution is nearly what is required. The CF you suggested requires an additional cell (B5) to display the duration (B3-B4) and it changes the color of cell B5, but I need the color of B4 (the user-specified date) to change color. There are many cells on the worksheet in which a user has to enter a date, so if possible it would be preferable to not have to add additional cells to display the duration. If a formula is used in the CF, will this help? Thank you for your help and quick response - RRP333 "Moo" wrote: Hi. To do the following, set up the following example: In cell B3, type: =NOW() and format it to Date (dd/mm/yyyy) In cell B4, type: 16/01/2005 (this is the user defined date) In cell B5, type: =DAYS360(B4,B3) Now set cell B5's colour to Green (select the cell, right click, Cell Properties, Pattern, and select the Green colour) Now, while still on B5, select Format Conditional Formatting. First row should read "Cell Value Is", "less than or equal to", then type 10. Click Format, and set the pattern to RED. Now select "Add" Second row should read "Cell Value Is", "less than or equal to", then type 20. Click Format, and set the pattern to ORANGE. Now select "Add" Second row should read "Cell Value Is", "less than or equal to", then type 30. Click Format, and set the pattern to YELLOW. This will work, as it excludes the need to look for a 4th condition (a limit in Excel Conditional Formatting). If you wanted RED to be the default colour, change all "less than or equal to" statements to "greater than". The NOW() statement prevents you needing to type in todays date. "RRP333" wrote: Dear Experts, I would like my users to be able to input a date in a cell, then Excel automatically calculates the elapsed duration in days from the inputted date to today's date, and changes the color of the cell to one of four colors, based on the 3 user-specified durations that correspond to the three colors. So if the user-specified durations are 10 days (if duration d =< 10, cell color = red), 20 (if 10< d =<20, cell color = orange), and 30 (if 20< d =<30, cell color = yellow, and if d30 cell color = green), the cell color will automatically change based on today's date. Ideally, the user-specified thresholds can be different for each cell, or at least for each column. Can this be done with conditional formatting or is code required? I'm new at both so any help would be greatly appreciated! Thank you, RRP333 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change cell color based on date
If you copy formatting from one cell to another, the conditional formatting is
copied, too. Just be sure that you use absolute references to the cells that contain the threshold values. As far as DAYS360 is concerned, there are other ways of calculating time intervals; they are explained in Help for the financial functions that need to convert days into a fraction of a year, i.e. PRICE, YIELD, YEARFRAC, etc. On Tue, 1 Mar 2005 19:49:02 -0800, RRP333 wrote: Hi Myrna, You champion! Thank you so much. Using the CFs you suggested does the trick, especially in the order you suggested. My only modification is the formula is today()-B4, since the user-specified dates are past dates not future dates. Also, rather than "hard-coding" the threshold durations (10, 20, 30), the formula includes a cell ref so a user can easily change the threshold durations at which he wants the colors to change. The worksheet will include cells in which the user can enter the desired threshold durations that are to apply to a column of dates, as the threshold durations will vary from column to column (each column represents a different business task or business group). The only potential hassle I see is that each cell will require setting up the CF. Currently there are about 3000 cells and the worksheet will grow to over 10000 or more cells. Perhaps as the worksheet grows, I wonder if code may be more appropriate rather than entering/maintaining all the CFs. Your explanation of the algorithms behind DAYS 360 is also very good. I now can apply that for other future opportunities. Thank you for your quick reply and solution - RRP333 "Myrna Larson" wrote: I think you've been given bad advice. As you suspect, you don't need an extra column for this, you can use a formula in the conditional formatting, AND you definitely don't want to use the DAYS360 function to calculate the difference between two dates. You get the number of days between 2 dates by simple subtration. The DAYS360 function is used by bond traders to get around the problems introduced by months of different lengths. For example, it would tell you that the difference between Jan 15 and Feb 15 is 30 days, and the difference between Feb 15 and March 15 is also 30 days. I assume, since you are interested in differences of 10 and 20 days, that you want actual differences, which are 31 and 28 days, respectively. Assuming the date in B4 is a future date. You need 3 conditional formatting formulas, applied in this order =B4-TODAY()30 =B4-TODAY()20 =B4-TODAY()10 On Tue, 1 Mar 2005 18:07:04 -0800, RRP333 wrote: Hi Moo, Thank you for quick reply. Your solution is nearly what is required. The CF you suggested requires an additional cell (B5) to display the duration (B3-B4) and it changes the color of cell B5, but I need the color of B4 (the user-specified date) to change color. There are many cells on the worksheet in which a user has to enter a date, so if possible it would be preferable to not have to add additional cells to display the duration. If a formula is used in the CF, will this help? Thank you for your help and quick response - RRP333 "Moo" wrote: Hi. To do the following, set up the following example: In cell B3, type: =NOW() and format it to Date (dd/mm/yyyy) In cell B4, type: 16/01/2005 (this is the user defined date) In cell B5, type: =DAYS360(B4,B3) Now set cell B5's colour to Green (select the cell, right click, Cell Properties, Pattern, and select the Green colour) Now, while still on B5, select Format Conditional Formatting. First row should read "Cell Value Is", "less than or equal to", then type 10. Click Format, and set the pattern to RED. Now select "Add" Second row should read "Cell Value Is", "less than or equal to", then type 20. Click Format, and set the pattern to ORANGE. Now select "Add" Second row should read "Cell Value Is", "less than or equal to", then type 30. Click Format, and set the pattern to YELLOW. This will work, as it excludes the need to look for a 4th condition (a limit in Excel Conditional Formatting). If you wanted RED to be the default colour, change all "less than or equal to" statements to "greater than". The NOW() statement prevents you needing to type in todays date. "RRP333" wrote: Dear Experts, I would like my users to be able to input a date in a cell, then Excel automatically calculates the elapsed duration in days from the inputted date to today's date, and changes the color of the cell to one of four colors, based on the 3 user-specified durations that correspond to the three colors. So if the user-specified durations are 10 days (if duration d =< 10, cell color = red), 20 (if 10< d =<20, cell color = orange), and 30 (if 20< d =<30, cell color = yellow, and if d30 cell color = green), the cell color will automatically change based on today's date. Ideally, the user-specified thresholds can be different for each cell, or at least for each column. Can this be done with conditional formatting or is code required? I'm new at both so any help would be greatly appreciated! Thank you, RRP333 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change color of point in series based on date | Charts and Charting in Excel | |||
Change tab color based on current color of a cell | Excel Discussion (Misc queries) | |||
Excel: Syntax to change cell color based on color of another cell | Excel Worksheet Functions | |||
cell color change based on due date | New Users to Excel | |||
Browse Forms Controls and change TextBox color based on cell color | Excel Programming |