Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a cell color based on date entry ...
Looking for solution to problem if anyone can help please ...
Column A contains a list of date values (e.g. 11/04/2005). I'm looking for a way to change the cell color based on the following criteria ... If the date falls earlier than today but no earlier than a week before today then cell color to be red. If the date falls earlier than today but no earlier than two weeks before today then cell color to be yellow. If the date falls earlier than today but no earlier than three weeks before today then cell color to be green. I have used the sample code as detailed here ... http://tinyurl.com/3cex5 (kindly advised by Max in microsoft.public.excel) and need to tailor the code to suit the different date ranges. Can anyone help out please as I'm not sure how to build this is to the CASE statements. Many thanks. -- T. Denford. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a cell color based on date entry ...
Hello -
Do you need macro code for that or could you use conditional formatting? For conditional formatting do the following: - Select the cell - Format Conditional Formatting... - Condition 1 "Cell Value is" "less than" "=TODAY()-14 - Format... Button Pattern Tab Select Red - Add Button ... for Condition 2 - Condition 2 "Cell Value is" "less than" "=TODAY()-7 - Format... Button Pattern Tab Select Yellow - Add Button ... for Condition 3 - Condition 3 "Cell Value is" "less than" "=TODAY() - Format... Button Pattern Tab Select Green You can use the following VB code to add the conditional formatting to the currently selected cell: Range("K16").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ Formula1:="=TODAY()-14" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ Formula1:="=TODAY()-7" Selection.FormatConditions(2).Interior.ColorIndex = 6 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ Formula1:="=TODAY()" Selection.FormatConditions(3).Interior.ColorIndex = 10 Selection.NumberFormat = "m/d/yyyy" I think that would be the easiest way to do it if you want the color to be updated upon entry. The other option would be to put code in the Worksheet_Change() callback but that can get tricky too. Joe |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a cell color based on date entry ...
On 13 Apr 2005 03:56:10 -0700, Joe HM wrote:
Hello - Do you need macro code for that or could you use conditional formatting? For conditional formatting do the following: - Select the cell - Format Conditional Formatting... - Condition 1 "Cell Value is" "less than" "=TODAY()-14 - Format... Button Pattern Tab Select Red - Add Button ... for Condition 2 - Condition 2 "Cell Value is" "less than" "=TODAY()-7 - Format... Button Pattern Tab Select Yellow - Add Button ... for Condition 3 - Condition 3 "Cell Value is" "less than" "=TODAY() - Format... Button Pattern Tab Select Green You can use the following VB code to add the conditional formatting to the currently selected cell: Range("K16").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ Formula1:="=TODAY()-14" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ Formula1:="=TODAY()-7" Selection.FormatConditions(2).Interior.ColorIndex = 6 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ Formula1:="=TODAY()" Selection.FormatConditions(3).Interior.ColorIndex = 10 Selection.NumberFormat = "m/d/yyyy" I think that would be the easiest way to do it if you want the color to be updated upon entry. The other option would be to put code in the Worksheet_Change() callback but that can get tricky too. Joe Hi Joe, Yes I need VB code to do this as I need to build in more than 3 conditional formats. -- T. Denford. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a cell color based on date entry ...
Hello T -
Here we go ... the following code applies the colors to the A1 Cell but you can tweak it to work for a range or whatever you need ... Private Sub Worksheet_Change(ByVal aTarget As Range) If aTarget.Column = 1 And aTarget.Row = 1 Then Set lTargetCell = ThisWorkbook.Sheets("Sheet1").Cells(aTarget.Row, aTarget.Column) If lTargetCell.Value < Date - 14 Then lTargetCell.Interior.Color = vbRed ElseIf lTargetCell.Value < Date - 7 Then lTargetCell.Interior.Color = vbYellow ElseIf lTargetCell.Value < Date Then lTargetCell.Interior.Color = vbGreen End If End If End Sub Joe |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a cell color based on date entry ...
Just to add -
After applying a constant to a cell colour format, Excel matches the colour to the nearest that exists in the palette, then applies the colorindex associated with the nearest matching colour. In other words, if the constant as an RGB value does not exist in the palette the closest according to Excel's (not very good) colour match algorithm is applied. All the Enum constants given by Patrick do exist in Excel's Default palette, and so will be matched exactly (assuming an uncustomized palette). Some of these, but not the Pink & Brown, could be replaced by vb constants that already exist, eg vbRed, vbYellow. vbBlue is not the same as Patrick's Enum Blue, colorindex's 5 & 41 respectively. I find it's somewhat slower to apply an RGB colour rather than a colorindex, but unlikely to be noticed in such a routine. Regards, Peter T "Patrick Molloy" wrote in message ... This may be of some help. Add the following code to a standard code module Option Explicit Enum eColor White = 16777215 Blue = 16737843 Red = 255 Green = 65280 Brown = 13209 yellow = 65535 Pink = 13408767 End Enum Sub GetAndSetColors() Dim index As Long index = 1 Do Until Cells(index, "A") = "" SetColor Cells(index, "A") index = index + 1 Loop End Sub Private Sub SetColor(target As Range) Dim clr As Long Select Case True Case target.Value = Date clr = eColor.White Case target.Value (Date - 7) clr = eColor.Red Case target.Value (Date - 14) clr = eColor.yellow Case target.Value (Date - 21) clr = eColor.Green Case Else clr = eColor.Pink End Select target.Interior.Color = clr End Sub In the Worksheet's change event, add a call to the GetAndSetColors procedu- Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 Then GetAndSetColors End If End Sub Using ENUM allows you to create your own variable to which you can assign colors - and this makes your code much mor ereadable. Also, using the SELECT CASE again makes your code more manageable ...just add more CASE staements...and its so much easier to read and debug than shed loads of IF...ELSEIF statements HTH "T. Denford" wrote: Looking for solution to problem if anyone can help please ... Column A contains a list of date values (e.g. 11/04/2005). I'm looking for a way to change the cell color based on the following criteria ... If the date falls earlier than today but no earlier than a week before today then cell color to be red. If the date falls earlier than today but no earlier than two weeks before today then cell color to be yellow. If the date falls earlier than today but no earlier than three weeks before today then cell color to be green. I have used the sample code as detailed here ... http://tinyurl.com/3cex5 (kindly advised by Max in microsoft.public.excel) and need to tailor the code to suit the different date ranges. Can anyone help out please as I'm not sure how to build this is to the CASE statements. Many thanks. -- T. Denford. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a cell color based on date entry ...
colorindex as opposed to color may also produce odd results if the user is
playing with their color palette. for instance my nice pale grey heading became a bright yellow on a colleagues machine ! "Peter T" <peter_t@discussions wrote in message ... Just to add - After applying a constant to a cell colour format, Excel matches the colour to the nearest that exists in the palette, then applies the colorindex associated with the nearest matching colour. In other words, if the constant as an RGB value does not exist in the palette the closest according to Excel's (not very good) colour match algorithm is applied. All the Enum constants given by Patrick do exist in Excel's Default palette, and so will be matched exactly (assuming an uncustomized palette). Some of these, but not the Pink & Brown, could be replaced by vb constants that already exist, eg vbRed, vbYellow. vbBlue is not the same as Patrick's Enum Blue, colorindex's 5 & 41 respectively. I find it's somewhat slower to apply an RGB colour rather than a colorindex, but unlikely to be noticed in such a routine. Regards, Peter T "Patrick Molloy" wrote in message ... This may be of some help. Add the following code to a standard code module Option Explicit Enum eColor White = 16777215 Blue = 16737843 Red = 255 Green = 65280 Brown = 13209 yellow = 65535 Pink = 13408767 End Enum Sub GetAndSetColors() Dim index As Long index = 1 Do Until Cells(index, "A") = "" SetColor Cells(index, "A") index = index + 1 Loop End Sub Private Sub SetColor(target As Range) Dim clr As Long Select Case True Case target.Value = Date clr = eColor.White Case target.Value (Date - 7) clr = eColor.Red Case target.Value (Date - 14) clr = eColor.yellow Case target.Value (Date - 21) clr = eColor.Green Case Else clr = eColor.Pink End Select target.Interior.Color = clr End Sub In the Worksheet's change event, add a call to the GetAndSetColors procedu- Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 Then GetAndSetColors End If End Sub Using ENUM allows you to create your own variable to which you can assign colors - and this makes your code much mor ereadable. Also, using the SELECT CASE again makes your code more manageable ...just add more CASE staements...and its so much easier to read and debug than shed loads of IF...ELSEIF statements HTH "T. Denford" wrote: Looking for solution to problem if anyone can help please ... Column A contains a list of date values (e.g. 11/04/2005). I'm looking for a way to change the cell color based on the following criteria ... If the date falls earlier than today but no earlier than a week before today then cell color to be red. If the date falls earlier than today but no earlier than two weeks before today then cell color to be yellow. If the date falls earlier than today but no earlier than three weeks before today then cell color to be green. I have used the sample code as detailed here ... http://tinyurl.com/3cex5 (kindly advised by Max in microsoft.public.excel) and need to tailor the code to suit the different date ranges. Can anyone help out please as I'm not sure how to build this is to the CASE statements. Many thanks. -- T. Denford. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a cell color based on date entry ...
colorindex as opposed to color may also produce odd results if the user is
playing with their color palette. for instance my nice pale grey heading became a bright yellow on a colleagues machine ! I totally agree! I play with palettes a lot !! Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Just to add - After applying a constant to a cell colour format, Excel matches the colour to the nearest that exists in the palette, then applies the colorindex associated with the nearest matching colour. In other words, if the constant as an RGB value does not exist in the palette the closest according to Excel's (not very good) colour match algorithm is applied. All the Enum constants given by Patrick do exist in Excel's Default palette, and so will be matched exactly (assuming an uncustomized palette). Some of these, but not the Pink & Brown, could be replaced by vb constants that already exist, eg vbRed, vbYellow. vbBlue is not the same as Patrick's Enum Blue, colorindex's 5 & 41 respectively. I find it's somewhat slower to apply an RGB colour rather than a colorindex, but unlikely to be noticed in such a routine. Regards, Peter T "Patrick Molloy" wrote in message ... This may be of some help. Add the following code to a standard code module Option Explicit Enum eColor White = 16777215 Blue = 16737843 Red = 255 Green = 65280 Brown = 13209 yellow = 65535 Pink = 13408767 End Enum Sub GetAndSetColors() Dim index As Long index = 1 Do Until Cells(index, "A") = "" SetColor Cells(index, "A") index = index + 1 Loop End Sub Private Sub SetColor(target As Range) Dim clr As Long Select Case True Case target.Value = Date clr = eColor.White Case target.Value (Date - 7) clr = eColor.Red Case target.Value (Date - 14) clr = eColor.yellow Case target.Value (Date - 21) clr = eColor.Green Case Else clr = eColor.Pink End Select target.Interior.Color = clr End Sub In the Worksheet's change event, add a call to the GetAndSetColors procedu- Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 Then GetAndSetColors End If End Sub Using ENUM allows you to create your own variable to which you can assign colors - and this makes your code much mor ereadable. Also, using the SELECT CASE again makes your code more manageable ...just add more CASE staements...and its so much easier to read and debug than shed loads of IF...ELSEIF statements HTH "T. Denford" wrote: Looking for solution to problem if anyone can help please ... Column A contains a list of date values (e.g. 11/04/2005). I'm looking for a way to change the cell color based on the following criteria ... If the date falls earlier than today but no earlier than a week before today then cell color to be red. If the date falls earlier than today but no earlier than two weeks before today then cell color to be yellow. If the date falls earlier than today but no earlier than three weeks before today then cell color to be green. I have used the sample code as detailed here ... http://tinyurl.com/3cex5 (kindly advised by Max in microsoft.public.excel) and need to tailor the code to suit the different date ranges. Can anyone help out please as I'm not sure how to build this is to the CASE statements. Many thanks. -- T. Denford. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Cell Color Based on Value | Excel Worksheet Functions | |||
Change Text Color in one cell based upon entry in referenced cell | Excel Discussion (Misc queries) | |||
Changing color of single bar based on x-axis date value | Charts and Charting in Excel | |||
Changing the color of a list entry based on a tolerance | Excel Worksheet Functions | |||
Changing cell color based on its value | Excel Programming |