Conditional Formatting

[Emma35]

Hi Everyone,
I have a form which allows users to enter weight results. On the left is a text box where the user enters the correct weight and on the right is a corresponding text box where they would enter the actual weight recorded. I'd like to know if i could put some conditional formatting into the text box on the right to make the text turn red if the actual weight recorded is above or below a certain amount for each weight. I've attached a screenshot of what i'm trying to do. Sorry if this is a little confusing. The values on the left hand side could be different each time and each value has a different tolerance above and below the correct weight.

Thanks a lot

 

[June7]

Have you tried to build Conditional Formatting rule?

 

[Gasman]

I believe you would need to use the tolerance to determine the ranges and then compare against those to determine if the condition is true.?

The you could perhaps use NOT BETWEEN, or < AND > in the CF formula.

 

[isladogs]

Is the tolerance range a set % above / below the entered value?
If so, I think you can do this with a single CF expression.

However if the tolerances vary by some other set of rules, you will need a CF rule for each of these. If so, you will need to be careful of the order of the CF expressions to ensure each is checked in turn.

BTW as I was recently accused of being pedantic, I'll reinforce that view by stating it should be MASS not WEIGHT

 

[Emma35]

Cheers for the suggestions guys. Yes i have actually come up with a CF formula which can do this for a set deviation but because the boxes on the left can change and have any one of about 20 values, i can't get the formula to change with it. I'm attaching an example of the deviations for each weight

 

[arnelgp]

put your Test weights to a table:

TestWeight Deviation
-----------------------------------
50 0.1
100 0.1
150 0.2
…
…

then you need a VBA for the textbox to the right on its BeforeUpdate

Private Sub TextRight1_BeforeUpdate(Cancel As Integer)
static lngOldColor As Long
Dim dblResult As Double
if lngOldColor = 0 Then lngOldColor = Me.TextRight.ForeColor
dblResult = Nz(DLookup("Deviation", "tblDeviation","TestWeight = " & Me.TextLeft1), 0)
If Me.TextRight1 >= (Me.TextLeft1 - dblDeviation) And _
                         Me.TextRight1 <= (Me.TextLeft1 + dblDeviation)
    Me.TextRight1.ForeColor =  lngOldColor
Else
    Me.TextRight1.ForeColor =  vbRed
End If
End Sub

of course you first validate the entry on the Left textbox if it is in tblDeviation:

Private Sub TextLeft1_BeforeUpdate(Cancel As Integer)

Cancel = (Nz(DCount("1", "tblDeviation", "TestWeight=" & Me.TextLeft1), 0) = 0)
If Cancel Then
    Msgbox "Not a valid test weight"
End If
End Sub

 

[Emma35]

Thanks for your help. Here's my code now with your suggestion

Private Sub Wt1_BeforeUpdate(Cancel As Integer)
Dim dblResult As Double
dblResult = Nz(DLookup("Deviation", "tbl_Deviations", "TestWeight = " & Me.W1), 0)
If Me.Wt1 >= (Me.W1 - dblDeviation) And Me.Wt1 <= (Me.W1 + dblDeviation) Then
    'do nothing within acceptable range
Else
    Me.Wt1.ForeColor = vbRed
End If
End Sub

I get the error message attached when i type in the first result

 

[Emma35]

Update: I think the word 'Then' was missing ?. Now when i type in the first result it just goes straight to Red

Private Sub Wt1_BeforeUpdate(Cancel As Integer)
Static lngOldColor As Long
Dim dblResult As Double
If lngOldColor = 0 Then lngOldColor = Me.Wt1.ForeColor
dblResult = Nz(DLookup("Deviation", "tbl_Deviations", "TestWeight = " & Me.W1), 0)
If Me.Wt1 >= (Me.W1 - dblDeviation) And _
                         Me.Wt1 <= (Me.W1 + dblDeviation) Then
    Me.Wt1.ForeColor = lngOldColor
Else
    Me.Wt1.ForeColor = vbRed
End If
End Sub

 

[theDBguy]

Hi. Sorry for jumping in... I prefer using a tolerance table with both lower and higher limits. For example:

LowerLimit    HigherLimit    Tolerance
0             50             0.1
51            100            0.1
101           150            0.2
and so on...

You can then join your table with this one to calculate the color.

 

[arnelgp]

is Deviation constant, eg

50 (+-) 0.1 = 49.9 and 50.1

or is it a percentage, eg:

50 (+-) (50 * 0.1) = 45 and 55 

If Me.Wt1 >= (Me.W1 - (Me.W1 * dblDeviation)) And _
                         Me.Wt1 <= (Me.W1 + (Me.W1 * dblDeviation)) Then
    Me.Wt1.ForeColor = lngOldColor

 

[June7]

CF rule can call a VBA custom function.

Using VBA to set color property of control in form will not work in Continuous or Datasheet view. ALL instances of control will display same setting because there is only one control.

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[Emma35]

is Deviation constant, eg

50 (+-) 0.1 = 49.9 and 50.1

or is it a percentage, eg:

50 (+-) (50 * 0.1) = 45 and 55 

If Me.Wt1 >= (Me.W1 - (Me.W1 * dblDeviation)) And _
                         Me.Wt1 <= (Me.W1 + (Me.W1 * dblDeviation)) Then
    Me.Wt1.ForeColor = lngOldColor

No the deviation changes depending on the test weight being used. For example the 50g weight has a deviation of ± 0.1 but the 500g weight has a deviation of ± 0.5

 

[theDBguy]

No the deviation changes depending on the test weight being used. For example the 50g weight has a deviation of ± 0.1 but the 500g weight has a deviation of ± 0.5

Hi. So, did you create a table for the deviations?

 

[arnelgp]

yes I understand what you mean.
then use the formula which is correct in Wt1_BeforeUpdate()
1.
50g - (50g * 0.1) >= 50g <= 50g + (50g * 0.1)

If Me.Wt1 >= (Me.W1 - (Me.W1 * dblDeviation)) And _
                         Me.Wt1 <= (Me.W1 + (Me.W1 + dblDeviation)) Then
    Me.Wt1.ForeColor = lngOldColor
Else
    Me.Wt1.ForeColor = vbRed
End If

2.
50g - 0.1 >= 50g <= 50g + 0.1

If Me.Wt1 >= (Me.W1 - dblDeviation) And _
                         Me.Wt1 <= (Me.W1 + dblDeviation) Then
    Me.Wt1.ForeColor = lngOldColor
Else
    Me.Wt1.ForeColor = vbRed
End If

 

[Emma35]

I think i have the code in right but it still turns red regardless of whether it's in spec or not. I think it would be easier if i just upload it so you can have a look. The form is called frm_DataEntry

Thanks again

 

[Gasman]

I had the opposite. It stayed black.

I would have thought you would need the code in the afterupdate event, not the beforeupdate event.?

 

[Gasman]

I changed the code to

Private Sub Wt1_AfterUpdate()
Static lngOldColor As Long
Dim dblResult As Double
If lngOldColor = 0 Then lngOldColor = Me.Wt1.ForeColor
dblResult = Nz(DLookup("Deviation", "tbl_Deviations", "TestWeight = " & Me.W1), 0)
If Me.Wt1 >= (Me.W1 - (Me.W1 * dblDeviation)) And _
                         Me.Wt1 <= (Me.W1 + (Me.W1 + dblDeviation)) Then
    Me.Wt1.ForeColor = vbBlack 'lngOldColor
Else
    Me.Wt1.ForeColor = vbRed
End If
End Sub

Reason being, was if out of spec, it changed to red, but would not change back to black if then changed to be within spec.?

So no real need for lngOldColor ?

HTH

Edit:
Just noticed on test is W1 * deviation and the other is W1 + deviation?

Only one type of formula can be correct surely?

 

[arnelgp]

here check this.
i change the validation function into more generic.

 

[arnelgp]

mr.gasman, you forgot that dblDeviation is not defined.
it should be dblResult.

 

[Emma35]

It's a little better but i can still enter 45 in the first result field and it doesn't turn red. If i enter something below 45 it will turn red. The tolerance for the 50g weight is only 0.1 grams