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

Conditional Formatting Error

P: 55
I am trying to add more than three conditional formatting conditions such that
If the Moisture values is > 3.5 it turns the text box = Red
is between 2.35 - 3.25 , the text box = Green
is > 3.25 and < 3.5 = Yellow
is < 2.35 and >2.10 = Yellow
is < 2.10 = Red
How can i write a Code for the same ?

I tried this code but it asks me for the text range, can someone help me resolve this issue please ?

Expand|Select|Wrap|Line Numbers
  1. Select Case Me![txtRange]
  2.   Case Is > 3.5
  3.      Me![txtMoisture Values].ForeColor = vbRed
  4.   Case 2.35 To 3.5
  5.      Me![txtMoisture Values].ForeColor = vbGreen
  6.   Case 2.10 To 2.34
  7.      Me![txtMoisture Values].ForeColor = vbYellow
  8.   Case Is < 2.1
  9.      Me![txtMoisture Values].ForeColor = vbRed
  10.   Case Else
  11.      Me![txtMoisture Values].ForeColor = vbBlack
  12. End Select
May 14 '07 #1
Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,365
I am trying to add more than three conditional formatting conditions such that
If the Moisture values is > 3.5 it turns the text box = Red
is between 2.35 - 3.25 , the text box = Green
is > 3.25 and < 3.5 = Yellow
is < 2.35 and >2.10 = Yellow
is < 2.10 = Red
How can i write a Code for the same ?

I tried this code but it asks me for the text range, can someone help me resolve this issue please ?

Select Case Me![txtRange]
Case Is > 3.5
Me![txtMoisture Values].ForeColor = vbRed
Case 2.35 To 3.5
Me![txtMoisture Values].ForeColor = vbGreen
Case 2.10 To 2.34
Me![txtMoisture Values].ForeColor = vbYellow
Case Is < 2.1
Me![txtMoisture Values].ForeColor = vbRed
Case Else
Me![txtMoisture Values].ForeColor = vbBlack
End Select
Why not just use the conditional formatting wizard?

[txtRange] should be [txtMoistureValues]
May 17 '07 #2

ADezii
Expert 5K+
P: 8,634
I am trying to add more than three conditional formatting conditions such that
If the Moisture values is > 3.5 it turns the text box = Red
is between 2.35 - 3.25 , the text box = Green
is > 3.25 and < 3.5 = Yellow
is < 2.35 and >2.10 = Yellow
is < 2.10 = Red
How can i write a Code for the same ?

I tried this code but it asks me for the text range, can someone help me resolve this issue please ?

Select Case Me![txtRange]
Case Is > 3.5
Me![txtMoisture Values].ForeColor = vbRed
Case 2.35 To 3.5
Me![txtMoisture Values].ForeColor = vbGreen
Case 2.10 To 2.34
Me![txtMoisture Values].ForeColor = vbYellow
Case Is < 2.1
Me![txtMoisture Values].ForeColor = vbRed
Case Else
Me![txtMoisture Values].ForeColor = vbBlack
End Select
As previously stated on several occassions, [txtRange] and [txtMoisture Values] are Field names that I used solely for demonstration purposes. You must substitute your own Field Names.
May 18 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
I've had this problem before. If I remember correctly the value in the textboxes have to be converted to Double precision using CDbl before the Select case can deal wiith them as decimal fractions.

Expand|Select|Wrap|Line Numbers
  1. Me![txtRange].Value = CDbl(Me![txtRange])
  2. Me![txtMoistureLevels].Value = CDbl(Me![txtMoistureLevels])
  3.  
  4. Select Case Me![txtRange]
-----------

It might be worth a try here.
May 23 '07 #5

ADezii
Expert 5K+
P: 8,634
I've had this problem before. If I remember correctly the value in the textboxes have to be converted to Double precision using CDbl before the Select case can deal wiith them as decimal fractions.

Me![txtRange].Value = CDbl(Me![txtRange])
Me![txtMoistureLevels].Value = CDbl(Me![txtMoistureLevels])

Select Case Me![txtRange]
-----------

It might be worth a try here.
We initially went over this ground where Moisture Values and Range were stored internally as SINGLE Precision Numbers. The Text Boxes referring to these Fields should return Variants of SubType SINGLE which should not pose any problem with the Select Case Statement. It is a very good point that definately should be double checked.
May 24 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.