calculating no of days

  • Thread starter Thread starter crapit
  • Start date Start date
C

crapit

How to set the conditional formating such that if current cell which has a
date format is at least 14 days from system date, then the cell become black
in foreground in Visual basic?
 
One way, if I understand you correctly:

Private Sub Worksheet_Calculate()
Dim rCell As Range
For Each rCell In Range("A1:A4,B10:B20,J3")
With rCell.FormatConditions
.Delete
If IsDate(rCell.Value) Then
With .Add(Type:=xlExpression, _
Formula1:="=ABS(TODAY()-" & _
rCell.Address & ")>=14")
.Interior.ColorIndex = 1
End With
End If
End With
Next rCell
End Sub

Put this in the worksheet code module. Adjust range references as
desired.m
 
Does it only apply to the current month only ?
If there are some wording b4 the date, how to check?
 
I try to do it this way at the conditional formatting
E.g cell B2 At the condition
Formula is = b2-today <14

It work, but if the difference bcomes negative, the cell also get
highlighted!
So how do i check that the value must be > zero but < 14 ?
 
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(b2-TODAY()<14,b2-TODAY()>0)"
Selection.FormatConditions(1).Font.ColorIndex = 35
Selection.FormatConditions(1).Interior.ColorIndex = 56

How do I make it to apply to a certain range e.g b2:e26
 
Back
Top