slayer1957
Board Regular
- Joined
- Jan 9, 2017
- Messages
- 50
Good day,
I have cells when selected it gives a drop down and opens the calendar and a date can be selected in that cell. When the date is selected i am trying to let the cell change color green if the date is in future and show red when date is in past, basically showing next steps when it is overdue and still managed. When i do conditional formatting i can put date in past 2/2/2019 or future 4/6/2019, it always revert back to show the green, formula, when formula cell value is greater or equal to =today() it shows green and the one that does not work show cell value less than =today(). Basically what is happening the conditional formatting does not work, i have tried several solutions without luck. I do not know if it might be formatting problem as i have tried several means to change format dd/mm/2019 etc. I also have seen that you can use vba but do not know and cant seem to let this work as private sub.
You insert calender by means of developer tab, more controls, microsoft date and time picker 6.0 and link it with the code below to the range of cells.
</today())"
I have cells when selected it gives a drop down and opens the calendar and a date can be selected in that cell. When the date is selected i am trying to let the cell change color green if the date is in future and show red when date is in past, basically showing next steps when it is overdue and still managed. When i do conditional formatting i can put date in past 2/2/2019 or future 4/6/2019, it always revert back to show the green, formula, when formula cell value is greater or equal to =today() it shows green and the one that does not work show cell value less than =today(). Basically what is happening the conditional formatting does not work, i have tried several solutions without luck. I do not know if it might be formatting problem as i have tried several means to change format dd/mm/2019 etc. I also have seen that you can use vba but do not know and cant seem to let this work as private sub.
You insert calender by means of developer tab, more controls, microsoft date and time picker 6.0 and link it with the code below to the range of cells.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)'-------------'Adding date picker to column D, Sheet 2 is the Next Step Sheet
With Sheet2.DTPicker1
.Height = 20
.Width = 34
If Not Intersect(Target, Range("D3:D400")) Is Nothing Then
.Visible = True
.Top = Target.Top
.Left = Target.Offset(0, 1).Left
.LinkedCell = Target.Address
Else
.Visible = False
End If
End With
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
With Sheet2.Range("D3:D400").FormatConditions
.Add Type:=xlExpression, Formula1:="=AND(B1<>"""",B1<today())"
With .Item(.Count)
.Interior.Color = 255
.SetFirstPriority
End With
End With