Find and Replace with Color Formatting in Microsoft Access Table

[aditya]

Hi,

I have a MS Access table where I marked few cells as "invalid". I am marking "invalid" at the end of cell value. Purpose to mark the cells is that I want to change the background of all the marked cells as Red colored.


I am performing this requirement currently by exporting the table to MS Excel and then performing Find and Replace with Format.

Question : is there any wayout to perform the same operation in MS Access?

Thanks in advance

[isladogs]

Use Conditional Formatting - that's what its designed for

[aditya]

Hi Ridders52,

Conditional Formatting in MS Access or MS Excel?

[CJ_London]

ridders is offline at the moment - in access. but in a form or report, unlike excel, tables are just for storing data

[isladogs]

Got the notification email - back now but already answered perfectly by ajax

[aditya]

Thanks Ajax,

I tried this option and it worked till now. My requirement is to export the formatted cells with colored background into excel. Problem is that I successfully changed the color of required cells in Access report but as soon as I export it to excel . All the color formatting removed automatically.
is there any solution to this problem?
Thanks in advance

[isladogs]

In that case you should do it in Excel

[CJ_London]

depends on how you are exporting, but typically only the underlying values are exported (or imported). You can preserve some formatting around numbers - number of dp for example, or what a date looks like, but not conditional formatting. There are things you can do with the format property - for example to show -123 as (123) but this would not appear to apply in your case, and I've not tried it to see if this would export to excel.

[aditya]

depends on how you are exporting, but typically only the underlying values are exported (or imported). You can preserve some formatting around numbers - number of dp for example, or what a date looks like, but not conditional formatting. There are things you can do with the format property - for example to show -123 as (123) but this would not appear to apply in your case, and I've not tried it to see if this would export to excel.

Thanks for the input. Following is my observation on this topic, please provide your point of view (if possible)
1. Created a report in Access
2. Apply conditional formatting and make necessary cells as Red colored
3. Export in Excel - Does not retain color formatting.
4. Export in PDF - All Good with colors also.

Expectation : I am currently doing the same thing in excel but want to provide one click solution to client in Access so that he will just run everything in macro and get output in excel with colored cells.

is it possible with Access or should I stop trying ?

Thanks,
Aditya

[isladogs]

Why can't the user work entirely in Access or entirely in Excel?

[aditya]

Actually, user is currently working in excel entirely but there is a huge manual work user is doing. To remove this manual work I created a data cleansing solution in access and as result I am exporting a table with marking of wrong data in cells. At this point, possibly thinking very loud
I want to color the cells in access only t avoid all the manual work otherwise client is good with the current solution as well.

[CJ_London]

only way to do it would be with vba code, or (not tried) perhaps to export to an excel template which has the conditional formatting already set.

re vba code, plenty of examples on this and other forums.

[aditya]

Thanks Ajax. Thanks alot for inputs.

[isladogs]

Not sure why I didn't suggest the template idea as I used to do exactly that over 10 years ago
The attached zip file contains 2 Excel files - one is an empty template ; the other is the same with data exported from Access.

Its fairly simple but you could do more with the idea
One way of doing CF would be to export to Excel then record a macro whilst you apply the CF.
With minor adjustments, you could possibly use the macro code in Access so its applied automatically

[aditya]

Thanks alot Ajax.