Conditional formatting not working with Datepicker

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.

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
</today())"
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,624
Latest member
gregg777

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top