IainMc2013
New member
- Local time
- Today, 04:25
- Joined
- Apr 1, 2013
- Messages
- 7
I have a table (Access 2000) that I am displaying in a form in datasheet format. I have set conditional formatting colours from the menu so that most fields are coloured depending on the status of the list item (4 different formats including the default) and this is working fine.
However I want to add some additional conditional formatting to only three fields in order to flag up some of the items that are due soon and also to denote the importance. I have written VBA code to do this but instead of applying the conditional formatting to individual rows it seems to be applying the first condition to the entire list. Here is my code:
Any ideas what I need to do? I'm sure its something really obvious but late Friday afternoon is probably not the best time to get my head around it! lol
I'm using long date format throughout the DB because it seemed to be the only way around UK and US date format conflicts in queries.
However I want to add some additional conditional formatting to only three fields in order to flag up some of the items that are due soon and also to denote the importance. I have written VBA code to do this but instead of applying the conditional formatting to individual rows it seems to be applying the first condition to the entire list. Here is my code:
I know some of the VB color values are wrong (VBOrange came up black) but I'll deal with that once I get this sorted. All of the cells for "Action" and "Due_Date" are showing up red, regardless of the date value (the first value in the table is a week from now). However if I reorder the list so that a date > 4 weeks from now tops the list all the records change to yellow. So clearly the code is taking the first value and applying to the rest of the list.Option Compare Database
Private Sub Form_Load()
Call ColourConfiguration
End Sub
Function ColourConfiguration()
' Action, Due_Date, Importance to be color ranked based on the priority score
If CDate(Me.Due_Date.Value) < DateAdd("d", 1, Format(Now, "dd MMMM yyyy")) Then ' action is due today or overdue
Me.Action.FormatConditions(0).BackColor = vbRed
Me.Action.FormatConditions(0).ForeColor = vbYellow
Me.Action.FormatConditions(1).BackColor = vbRed
Me.Action.FormatConditions(1).ForeColor = vbYellow
Me.Due_Date.FormatConditions(0).BackColor = vbRed
Me.Due_Date.FormatConditions(0).ForeColor = vbYellow
Me.Due_Date.FormatConditions(1).BackColor = vbRed
Me.Due_Date.FormatConditions(1).ForeColor = vbYellow
Else
End If
If CDate(Me.Due_Date.Value) < DateAdd("d", 28, Format(Now, "dd MMMM yyyy")) Then ' action is due within 28 days
Me.Due_Date.FormatConditions(0).BackColor = vbRed
Me.Due_Date.FormatConditions(0).ForeColor = vbWhite
Me.Due_Date.FormatConditions(1).BackColor = vbRed
Me.Due_Date.FormatConditions(1).ForeColor = vbWhite
If Me.Importance = "High" Then
Me.Action.FormatConditions(0).BackColor = vbRed
Me.Action.FormatConditions(1).BackColor = vbRed
Me.Action.FormatConditions(0).ForeColor = vbWhite
Me.Action.FormatConditions(1).ForeColor = vbWhite
Else
Me.Action.FormatConditions(0).BackColor = vbOrange
Me.Action.FormatConditions(1).BackColor = vbOrange
End If
Else
End If
If (CDate(Me.Due_Date.Value) > DateAdd("d", 28, Format(Now, "dd MMMM yyyy"))) And (CDate(Me.Due_Date.Value) < DateAdd("d", 60, Format(Now, "dd MMMM yyyy"))) Then ' action due within 60 days
Me.Due_Date.FormatConditions(0).BackColor = vbYellow
Me.Due_Date.FormatConditions(1).BackColor = vbYellow
If Me.Importance = "High" Then
Me.Action.FormatConditions(0).BackColor = vbYellow
Me.Action.FormatConditions(1).BackColor = vbYellow
Else
Me.Action.FormatConditions(0).BackColor = vbGreen
Me.Action.FormatConditions(1).BackColor = vbGreen
End If
Else
End If
End Function
Any ideas what I need to do? I'm sure its something really obvious but late Friday afternoon is probably not the best time to get my head around it! lol
I'm using long date format throughout the DB because it seemed to be the only way around UK and US date format conflicts in queries.