calculating no of days

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?
 
J

JE McGimpsey

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
 
C

crapit

Does it only apply to the current month only ?
If there are some wording b4 the date, how to check?
 
F

Frank Kabel

Hi
not really sure what you're trying to do. Could you give some specific
examples?
 
C

crapit

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 ?
 
C

crapit

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
 
Top