Link to home
Start Free TrialLog in
Avatar of M A
M AFlag 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?
Avatar of als315
als315
Flag of Russian Federation image

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
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?
Avatar of 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
Avatar of 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
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.
May be you can upload your DB with this form and export code? Remove all sensitive data and compress DB before uploading.
Avatar of 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
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
Avatar of Hamed Nasr
Hamed Nasr
Flag of Oman image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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.
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.
Avatar of M A

ASKER

Many thanks