Conditional Formating Excel Form
 

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 is pre-set, they have to enter the Reported field and the Difference field is a calculated field based on Targeted - Reported.

If this difference is more than 10% or less than -10% I would like is 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 can explain the difference.

Any help would be much appreciated

Cheers

JP

Conditional Formating Excel Form
 

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" wrote:


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 is
pre-set, they have to enter the Reported field and the Difference field
is a calculated field based on Targeted - Reported.

If this difference is more than 10% or less than -10% I would like is
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 can
explain the difference.

Any help would be much appreciated

Cheers

JP




--
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

Conditional Formating Excel Form
 

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" wrote:


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
(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" wrote:
-

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
is
pre-set, they have to enter the Reported field and the Difference
field
is a calculated field based on Targeted - Reported.

If this difference is more than 10% or less than -10% I would like is
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
can
explain the difference.

Any help would be much appreciated

Cheers

JP




--
JoePineapples
-




--
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



[quote=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.control1me.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

This does exactly what was looking for:

Thanks for everyones input.