Avatar of M A
M A
Flag for United States of America asked on

high light a field in access by its priority and export

I have an access file with few forms. I have a combo named "priority" which has value "Low, medium, High". I want to highlight another field based on this value.
I have a button to export. I want to export to excel with the same field highlighted.

Anyone can have show a light to accomplish this?
Microsoft AccessVisual Basic ClassicVisual Basic.NET

Avatar of undefined
Last Comment
M A

8/22/2022 - Mon
als315

You didn't mentioned Access version. In Access 2007 and later you can do it with conditional formatting. Can you show your code for export? You can do it from vba or you can use Excel template with conditional formatting
Hamed Nasr

If priority is a field in record, then you can conditionally format a field depending on priority values.

Then yo can export the table.

Do you mean highlighting the exported cells in excel?
M A

ASKER
I am working on access2010 but users accessing from 2007. It is a shared access file.

Can you just guide me how to do conditional formatting using VBA
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
M A

ASKER
@hnasr
-->Do you mean highlighting the exported cells in excel?
yes I want to highlight cell in exported excel. Bcoz I dont want to highlight cells everytime when I export
Hamed Nasr

Highlight field in access:
In design mode, select control.- Ribbon-Format Tab-Control Formatting group -Conditional Formatting: New rule
Expression IS [PRIORITY] = "Low"
Set font color to a color
OK
New rule and repeat for other priorities.

Left Excel issue.

Can you export to the same sheet?
Then you can define a named area and set conditional formatting.
als315

May be you can upload your DB with this form and export code? Remove all sensitive data and compress DB before uploading.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
M A

ASKER
here is the file attached. rename the file extension from .docx to .rar

I want to highlight  field "payment_term" based on the value on "priority_combo"
Based on the same I want the the exported excel to be highlighted
PTC---Copy.docx
Hamed Nasr

Priority_combo is unbound. You need to have a field in MASTER_TB to hold priority for Excel conditional formatting to work. The combo on the form has no meaning.
Even the code in the After_update event, just changes background color without saving it to table.
SOLUTION
Hamed Nasr

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
M A

ASKER
@als315
I appreciate if you could tell me how you did the highlighting in excel file as when i change the field sequence it is highlighting another field.
Your help has saved me hundreds of hours of internet surfing.
fblack61
als315

Look at excel_format sub in Module1. All formatting is here:
For i = 2 To Lr
    If wks.cells(i, 34).Value = "Low" Then wks.cells(i, 16).Interior.Color = vbGreen
    If wks.cells(i, 34).Value = "Medium" Then wks.cells(i, 16).Interior.Color = vbYellow
    If wks.cells(i, 34).Value = "High" Then wks.cells(i, 16).Interior.Color = vbRed
Next i

Open in new window

16 - Column P, 34 - AH in R1C1 notation.
M A

ASKER
Many thanks