Conditional Formating Excel Form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JoePineapples - 30 Oct 2006 14:08 GMT

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

--
JoePineapples

Reply to this Message

WLMPilot - 30 Oct 2006 21:58 GMT

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

> Hi All
>
[quoted text clipped - 19 lines]
>
> JP

Reply to this Message

JoePineapples - 31 Oct 2006 09:34 GMT

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

WLMPilot Wrote:

> I can help you on the first part -- Conditional Format. Let's say we
> use A1
[quoted text clipped - 43 lines]
>
> JP

Signature

JoePineapples

Reply to this Message

Eddie Holder - 02 Nov 2006 17:12 GMT

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

> Hi Les
>
[quoted text clipped - 58 lines]
> >
> > JP

Reply to this Message

JoePineapples - 07 Nov 2006 08:50 GMT

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

Eddie Holder Wrote:

> Are you familiar with VB.
>
[quoted text clipped - 24 lines]
>
> Eddie

Signature

JoePineapples

Reply to this Message

Rate this thread: