RE: Access - Conditional Formatting

Hi Lisa,

I hope you found the training useful. Regarding to your question, there is a way to trigger the multiple conditional formats upon every change of colour entry.

1. Make the colour list on a chosen cell, DATA menu > Validation > choose List as your criteria
2. In the Source box, type in your colour list Red, Orange, Yellow, Light Green, Dark Green > press OK
3. Right click the Sheet tab and select View Code
4. Paste the following code under Option Explicit

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Value = "Red" Then
With ActiveCell
.Interior.ColorIndex = 3
.Font.Bold = True
.Font.ColorIndex = 3
End With

ElseIf ActiveCell.Value = "Orange" Then
With ActiveCell
.Interior.ColorIndex = 46
.Font.Bold = True
.Font.ColorIndex = 46
End With

ElseIf ActiveCell.Value = "Yellow" Then
With ActiveCell
.Interior.ColorIndex = 6
.Font.Bold = True
.Font.ColorIndex = 6
End With

ElseIf ActiveCell.Value = "Light Green" Then
With ActiveCell
.Interior.ColorIndex = 35
.Font.Bold = True
.Font.ColorIndex = 35
End With

ElseIf ActiveCell.Value = "Dark Green" Then
With ActiveCell
.Interior.ColorIndex = 10
.Font.Bold = True
.Font.ColorIndex = 10
End With

End If
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
N.B. Worksheet_Change is part of the event procedure choices on each worksheet. See p.20 in the book.

Hope this helps

Regards,

Katie
Microsoft Certified Tainer