Here is a routine I did years ago, there are 2 versions, version 1 is if the current record you want to highlight is on the Main Form, version 2, if it is on a Sub Form.
Version 1, paste this into your Main Form's "Current" event:
Dim ctl As Control, strForm As String
Dim varCondition As Variant
On Error Resume Next
varCondition = Me!RecordID 'Change this to your Control Name with the Record# (Ideally a Unique No.)
For Each ctl In Me.Controls
With ctl
If .Tag = "ConditionalFormat" Then
With Me.Controls(ctl.Name).FormatConditions _
.Delete 'Delete any other Conditional Formatting, limit is 3
End With
With Me.Controls(ctl.Name).FormatConditions _
.Add(acExpression, , "[RecordID]=" & varCondition)
.BackColor = 14024661 'Light Green
.ForeColor = 0 'Black
End With
End If
End With
Next ctl
Version 2, paste this into your Sub Form's "Current" event:
Dim ctl As Control, strForm As String
Dim varCondition As Variant
On Error Resume Next
varCondition = Me!RecordID 'Change this to your Control Name with the Record# (Ideally a Unique No.)
For Each ctl In Forms!YourFormName.YourSubFormName.Form.Controls
With ctl
If .Tag = "ConditionalFormat" Then
With Forms!YourFormName.YourSubFormName.Form.Controls(ctl.Name).FormatConditions _
.Delete 'Delete any other Conditional Formatting, limit is 3
End With
With Forms!YourFormName.YourSubFormName.Form.Controls(ctl.Name).FormatConditions _
.Add(acExpression, , "[RecordID]=" & varCondition)
.BackColor = 14024661 'Light Green
.ForeColor = 0 'Black
End With
End If
End With
Next ctl
In version 2, obviously, change YourFormName to yours and YourSubFormName to the name as it appears in the Main Form.
Let me know if you have any problems with it.