Conditional Formatting Macro

I have built a simple database to track the mechanical condition of
offshore equipment. We established a system of ranking for issues
that arise and used conditional formatting of the cell's background to
make these issues visible. White cell (default) indicates "No Issue",
Green "Low", Yellow "Medium" and Red "High". This is currently woking
fine.

Recently our corporate gurus have now come up with a 4th color, Amber
"Significant" which sits between the Yellow and Red. Since one has
only 3 standard conditions available we determined that we need to
write a macro in order to use this 4th color.

I do not have a clue how to even start building this macro. HELP!!

re: Conditional Formatting Macro

"WU10" <cliff_wooten@hotmail.comwrote in message
news:1172828857.782836.51900@j27g2000cwj.googlegro ups.com...You need to put some code in the form's After Update event, something like
(untested):

If Me.txtMyTextBox = "No Issue" Then
Me.txtMyTextBox.BackColor = xxx
ElseIf Me.txtMyTextBox = "Low" Then
Me.txtMyTextBox.BackColor = yyy
ElseIf Me.txtMyTextBox = "Medium" Then
Me.txtMyTextBox.BackColor = zzz
Else
Me.txtMyTextBox.BackColor = nnn
End If

This assumes that your "cell" is a text box that is bound to the field that
contains the "Low", "Medium" etc data, that the text box name is
"txtMyTextBox" and that xxx, yyy, zzz and nnn are the VBA numbers for the
colours you require. You'd also need to call this code from the form's
Current event.

HTH - Keith.
www.keithwilby.com

re: Conditional Formatting Macro

Not sure what you mean by "call this code from the form's
Current event".

Worked with this over the weekend by typing in the code you supplied
(shown below). Now get my 5 diferrent conditions (4-colours + white)
but when I enter a change in the MIStatus textbox the colours change
for the cell updated plus all of the 200+ MIStatus cells regardless of
the status in each cell.

How can I prevent this from occuring?

Private Sub MIStatus_AfterUpdate()

'C1 = Healthy (No Issue - White Background)
'C2 = Low (Green Background)
'C3 = Medium (Yellow Background)
'C4 = Significant (Amber Background)
'C5 = High (Red Background)

If Me.MIStatus = "C2" Then
Me.MIStatus.BackColor = 65280
ElseIf Me.MIStatus = "C3" Then
Me.MIStatus.BackColor = 65535
ElseIf Me.MIStatus = "C4" Then
Me.MIStatus.BackColor = 43257
ElseIf Me.MIStatus = "C5" Then
Me.MIStatus.BackColor = 255
Else: Me.MIStatus.BackColor = -2147483643
End If

End Sub

re: Conditional Formatting Macro

"WU10" <cliff_wooten@hotmail.comwrote in message
news:1173086287.637056.135490@8g2000cwh.googlegrou ps.com...In the current event module put "Call MIStatus_AfterUpdate"
I'm guessing that you are using a continuous form - the code will only work
on a single form. I'm not aware of any method to have a single control in
different colours for different records.

Keith.