Macro into VB

[sheldon]

Hi,

I am struggling with some code I recorded as a macro and was changed into code for VB.

The Macro looked at the particular cell, if it was under 8 days after todays date it made the text green and if the date was over 8 days it made it red. But the code I have simply turns the text red and does not change with the date. Could you please help?

This is what I have now...

With Me.Cells(.Row, "N")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=(TODAY()>=K" & .Row & "+8)"
With .FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=(TODAY()<=K" & .Row & "+8)"
With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 50
End With
End With

[Bob Phillips]

sheldon

It is difficult to be absolutely sure what you are doing, you have omitted code (for instance, what is the qualifying object to the .Row statements), but generally you have to be careful what cell is active when creating CF and adjust to that cell. For instance, when I ran the code (suitably qualifying .Row with the activecell). the formula in N ended up as =(TODAY()>=S9+8), note the K has become S.

What are you getting in N, and what about the rest of the code, what is triggereing it?

[sheldon]

N is a column which has a formula enter. This formula is entered from a VB form, which creates a new record for the row. The code is in the Worksheet, so triggered by the new entry of a record.

If this helps, this is where the code sits...


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<== change to suit
Const ADD_FORMULA = "=IF(M<rownum><>"""",""Returned"",IF(AND(TODAY()>=K<rownum>+8,M<rownum>=""""),""Require Chasing"",""No Action Required""))"
Const ADD_FORMULAa = "=K<rownum>+8"
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value <> "" Then
Me.Cells(.Row, "N").Formula = Replace(ADD_FORMULA, "<rownum>", .Row)
With Me.Cells(.Row, "N")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=(TODAY()>=K" & .Row & "+8)"
With .FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=(TODAY()<=K" & .Row & "+8)"
With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 50
End With
End With


End If
If .Value <> "" Then
Me.Cells(.Row, "L").Formula = Replace(ADD_FORMULAa, "<rownum>", .Row)
Me.Cells(.Row, "L").NumberFormat = "dd/mm/yyyy"
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

[Bob Phillips]

See if this works better



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<== change to suit
Const ADD_FORMULA = "=IF($M<rownum><>"""",""Returned"",IF(AND(TODAY()>=$K<rownum>+8,$M<rownum>=""""),""Require Chasing"",""No Action Required""))"
Const ADD_FORMULAa = "=K<rownum>+8"

On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

If .Value <> "" Then

Me.Cells(.Row, "N").Formula = Replace(ADD_FORMULA, "<rownum>", .Row)
With Me.Cells(.Row, "N")

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=(TODAY()>=$K" & .Row & "+8)"
With .FormatConditions(1).Font

.Bold = True
.Italic = False
.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=(TODAY()<=$K" & .Row & "+8)"
With .FormatConditions(2).Font

.Bold = True
.Italic = False
.ColorIndex = 50
End With
End With
End If

If .Value <> "" Then

Me.Cells(.Row, "L").Formula = Replace(ADD_FORMULAa, "<rownum>", .Row)
Me.Cells(.Row, "L").NumberFormat = "dd/mm/yyyy"
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

[sheldon]

Sorry, didnt make a difference.

[Bob Phillips]

Can you give me an example of values in L and M so I can test what you see?

[sheldon]

K = 10/09/2008

L = 18/09/2008

I'v found that it is looking at the wrong date. when entered. The entry I tried was row 319, but it was looking at 321. Change the formatting to look at 319 and worked fine. Do you know how I would change it to look at the correct row?

Thanks

[Bob Phillips]

I am not seeing that behaviour. I changed A20, and the CF referred to row 20.