Conditional Formatting Based On Another Field

[jereece]

Is there a way to apply conditional formatting on a field based on the contents of another field?

For example, lets say I have the following fields

START_DATE
APPROVED_DATE
CAT#
DAYS_OPEN

The DAYS_OPEN field is automatically calculated in the query and is not a stored date.

My criteria is that if CAT# is 3, my days open should not exceed 30 but if my CAT# is 1 or 2, my days open should not exceed 60. I want to apply conditional formatting so that it turns yellow at approximately 80% of the date limit and red at approximately 90% of the date limit, otherwise the conditional formatting is green.

I know how to do conditional formatting, but no sure if it can be modified based on the content of another field.

Thanks for any suggestions.

Jim

 

[jereece]

Anyone have an idea?

Thanks,
Jim

 

[unclefink]

I just ran across this post as I was searching the same question. I was wondering if after 7 years you came up with a resolution?

 

[CJ_London]

Easy enough,

In the conditional formattig wizard, select 'expression is'

then write your formula (which should resolve to true or false) and then set your format. Put square brackets around control names and order the formulae in order of preference.

Using the below example, set the default back colour to green, the first conditional will be value>90% Red then second conditional will be >80% yellow.

Reason 95% is greater than both 90% and 80% so going in the other order would select 80% first

Hope that makes sense, but if you are familar with conditional formating, you'll understand!

 

[Shinta]

Greetings:

Just for helping anyone in my same situation; I was having troubles with the conditional formatting ... Googled for a couple of hours, this forum, etc.. :banghead:... until I reached this post.

The trick is as direct and simple as CJ_London said:

+ Go to "Design View".
+ Select the control that would be formatted.
+ Do Right-click -> Conditional Formatting, or select "Conditional" at the Design Tab.

"In the conditional formatting wizard, select 'expression is'

then write your formula (which should resolve to true or false) and then set your format. Put square brackets around control names and order the formulae in order of preference."

+ For the sake of clarity, I share my situation, where the conditioned Control was part of a subForm, and it needed to be compared to a control at the Main Form:

[MySubFormControlID]=[Forms]![MainForm]![ControlID]

Of course, you need to make sure that the formula would resolve satisfactory, as CJ_London states.

My deepest thanks to all the people at this community: you are all pretty professional and sincere about our real concerns

 

[Gismo]

Re: Conditional Formatting calculated field

Hi All,

I want to use conditional formatting to highlight a control of a calculated field.
The field is a joint control of 2 other controls
sample: Due: 90 Hrs, the value is combined with a unit of measure , in this case it is hours. i want to highlight when the value is equal or less than 100 hours. Not sure how to use the expression format in this case.

 

[June7]

You should have started your own thread instead of hijacking an old one. New, no-reply threads get more attention.

What field has the number value? Will the unit always be hours? If not, what should happen if the unit is not hours?

Either reference the field with the number or use Val() function to extract the number part.

 

[Gismo]

in will always be hours, i can extract the number value but i still want it to be displayed as a number with the unit of measure when highlighted.

Next due should be highlighted when equal or less the 100 HRS

 

[CJ_London]

use the format property - set it to something like

00 FH

 

[Gismo]

it is just not working for me, I guess I have no idea where to even start with this one.
I have another field which has only the value, not sure how to base the conditional on another field, in this case, I would not need to do any formatting on the control

 

[Gismo]

Found the solution
Thank you

 

[isladogs]

I would recommend avoiding calculated fields in tables and even more strongly not to include units like hours in your expression.
Nevertheless as that's what you seem to have done, here's a solution

==================================================
EDIT Got distracted and you solved it before I posted

Found the solution
Thank you

For the benefit of others reading this thread in the future, please describe your solution