By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,849 Members | 2,169 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,849 IT Pros & Developers. It's quick & easy.

Conditional Formatting Macro

P: n/a
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!!

Mar 2 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"WU10" <cl**********@hotmail.comwrote in message
news:11*********************@j27g2000cwj.googlegro ups.com...
>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!!
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
Mar 2 '07 #2

P: n/a
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
Mar 5 '07 #3

P: n/a
"WU10" <cl**********@hotmail.comwrote in message
news:11**********************@8g2000cwh.googlegrou ps.com...
Not sure what you mean by "call this code from the form's
Current event".
In the current event module put "Call MIStatus_AfterUpdate"
>
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.
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.
Mar 7 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.