Conditional Formating Excel Form

[JoePineapples]

Hi All

I have a Form in excel which lets people enter monitoring information


It has the fields Target, Reported and Difference. The Target field i
pre-set, they have to enter the Reported field and the Difference fiel
is a calculated field based on Targeted - Reported.

If this difference is more than 10% or less than -10% I would like i
the following:

A. To change the font colour in the Difference text box red.

and

B. The form to open another text box (called reason) - so that they ca
explain the difference.

Any help would be much appreciated

Cheers

J

 

[WLMPilot]

I can help you on the first part -- Conditional Format. Let's say we use A1
(Target), B1 (Reported) and C1 (Difference). Click on C1, then FORMAT >
CONDITIONAL FORMAT

Choose "Formula Is" instead of "Cell Value Is"

Type in: =AND(C1<>"",OR(C1/A1 < -0.1,C1/A1 > 0.1))

After typing in formula, click on FORMAT (in the conditional formatting
window and the FONT. There is a place to choose the color you wish to
display if the criteria is met.

Les

 

[JoePineapples]

Hi Les

Thanks for the advice and your time but this is not exactly what I am
looking for. I understand how to use conditional formating within an
excel spreadsheet.

What I am looking for is how to do it (if possible) within an Form as
created using the Visual Basic Editor. There doesn't seem to be an
obvious way to do it.

Thanks again.

JP

 

[Eddie Holder]

Are you familiar with VB.

If you have a form in VB, all controls will have events attached to them. If
you double click on the control which you enter the reoported field, choose
the event (top right dropdown in VB) and set it to after_update (or change).

Your code will look something like this.

sub starts
If me.control1<me.controlname2 then
me.control3.enable
msgbox " Enter a reason in reason field"
me.control3.interior.color=vbred
me.control3.setfocus
end if

sub ends

In the above example, me refers to the current userform. I also assume you
have named all controls etc.

Hope this helps

Eddie

 

[JoePineapples]

I have a very limited knowlege of vb.

I have the following code to resolve the first problem:

Private Sub txtDifference_change()
'Change colour to red if txtdifference 10% or -10%
If Left(txtDifference.Text, Len(txtDifference.Text) - 1) 10 Or
Left(txtDifference.Text, Len(txtDifference.Text) - 1) (-10) Then
txtDifference.ForeColor = &HFF&
Else
txtDifference.ForeColor = &H0&
End If

End Sub

I'm hoping this will form the basis of my next part.

Thanks

JP