How are ya jonney . . . . .
[blue]
ZmrAbdulla[/blue] is correct. You can only have [blue]
3 conditional formats[/blue] ([blue]actually 4 including default settings[/blue]). Therefore a max of 4 different colors.
If you don't mind [blue]alternating bewteen two colors[/blue] (1 format condition + default) whenever [purple]
SalesPersonID[/purple] changes, try the following:
[ol][li]The code requires [purple]
Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.[/li]
[li]A new field is required to hold a tag for the color, so add a field to the appropriate table.
[ol a][li]Name [blue]
colTag[/blue][/li]
[li]DataType [blue]
Yes/No[/blue][/li][/ol][/li]
[li]In a module in the modules window, copy/paste the following code ([blue]
you![/blue] substitute proper names in [purple]
purple[/purple]):
Code:
[blue]Public Sub SetColorTags()
Dim rst As DAO.Recordset, Color As Boolean, Hld
Set rst = Forms![purple][b]YourFormName[/b][/purple].RecordsetClone
With rst
If Not .BOF Then
.MoveNext
If Not .EOF Then
Do
Hld = !SalesPersonID
.MovePrevious
If !SalesPersonID <> Hld Then Color = Not Color
.MoveNext
.Edit
!ColTag = Color
.Update
.MoveNext
Loop Until .EOF
End If
End If
End With
Set rst = Nothing
[/blue]
[/li]
[li]Open the form in design view. If the [blue]
RecordSource[/blue] for the form is a [blue]
Query or SQL[/blue], make sure the new field [blue]
colTag[/blue] is included. The new field will work in the background, so don't add it to the form.[/li]
[li]In the [blue]
Open event[/blue] of the form, copy/paste the following:
Code:
[blue] Call SetColorTags[/blue]
[/li]
[li]The following code takes care of [blue]
adding new records[/blue]. In the [blue]
AfterUpdate event[/blue] of the form, copy/paste the following:
Code:
[blue]Private Sub Form_AfterUpdate()
Dim rst As DAO.Recordset, Hld, hldTag As Boolean
Set rst = Me.RecordsetClone
With rst
.FindFirst "[SalesPersonID] =" & Me!SalesPersonID
.MovePrevious
If Not .BOF Then
Hld = rst!SalesPersonID
hldTag = !ColTag
.MoveNext
.Edit
If !SalesPersonID <> Hld Then
!ColTag = Not hldTag
Else
!ColTag = hldTag
End If
.Update
End If
End With
Set rst = Nothing
End Sub[/blue]
[/li]
[li]Finally the Conditional Format. In form design view select [purple]
SalesPersonID[/purple]. Then click [blue]Format[/blue] - [blue]Conditional Formatting[/blue]. In the combobox select [purple]
Expression Is[/purple]. To the right of the combobox enter:
Code:
[blue][colTag]=True[/blue]
Select your colors and click OK.[/li][/ol]
[purple]Thats it! . . . . give it a whirl & let me know . . . .[/purple]
See Ya! . . . . . .