darkdarkwt
New Member
- Joined
- Jun 9, 2015
- Messages
- 3
The idea is to highlight the cell based on row beside
if the target date is less than 14 days from today then Cell F ( same row) will fill with orange colour
if less than 7 days then it will fill up with red colour
if more than 14 days it will be green in colour.
below is the code which i came out , nothing come out after i click run .
please help me >< and thank you very much .
if the target date is less than 14 days from today then Cell F ( same row) will fill with orange colour
if less than 7 days then it will fill up with red colour
if more than 14 days it will be green in colour.
below is the code which i came out , nothing come out after i click run .
please help me >< and thank you very much .
Code:
Sub Alert()
Dim RowNum As Integer
RowNum = 5
Do While RowNum <= 255
Range("F" & RowNum).FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND((D&RowNum-TODAY())<=7,(D&RowNum-TODAY())>=0)"
Range("F" & RowNum).FormatConditions(Range("F" & RowNum).FormatConditions.Count).SetFirstPriority
With Range("F" & RowNum).FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Range("F" & RowNum).FormatConditions(1).StopIfTrue = False
Range("F" & RowNum).FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(D&RowNum-TODAY())>14"
Range("F" & RowNum).FormatConditions(Range("F" & RowNum).FormatConditions.Count).SetFirstPriority
With Range("F" & RowNum).FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
End With
Range("F" & RowNum).FormatConditions(1).StopIfTrue = False
Range("F" & RowNum).FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND((D&RowNum-TODAY())<=14,(D&RowNum-TODAY())>7)"
Range("F" & RowNum).FormatConditions(Range("F" & RowNum).FormatConditions.Count).SetFirstPriority
With Range("F" & RowNum).FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Range("F" & RowNum).FormatConditions(1).StopIfTrue = False
RowNum = RowNum + 1
Loop