Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access Datasheet "Conditional Formatting" on multiple cells

Posted on 2014-04-15
4
Medium Priority
?
2,008 Views
Last Modified: 2014-04-19
See linked image.

http://screencast.com/t/j6eivFsZUP


I am availing of the facility to put "conditional formatting" on multiple cells in a datasheet.  Note the yellow hightlighting.

I notice the following;

(a) It is very easy to put conditional formatting onto multiple cells

(b) It SEEMS impossible to actually determine what cells you have formatted when you revisit the form.  I.e. As per the linked image - it only tells me that I have formatted "multiple" cells .... Access does NOT appear to tell me WHICH cells are included in this multiple ?

How do I know what cells are included?
0
Comment
Question by:Patrick O'Dea
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 200 total points
ID: 40003319
I don't think you can do this. As soon as more than one coloumn is marked, the Format Conditions button gets disabled.
The option to view format conditions for several coloumns is just for convenience.

/gustav
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 1200 total points
ID: 40003561
Hi,

after experimenting a little bit with CFs in A2010 it looks like this (having a form with three textboxes named A,B,C):

if you select A and B and enter a CF rule you can apply the rule to both fields, "multiple" means: All selected fields.
in my case I tried with "field value" and equal to x, the result was, A used "field value equal to "x" " in one field and "equal to [x]" in the other, so be careful... that means, both fields now have a different formula
when I reselect both fields and open the CF rule editor, "Multiple" again means all selected fields, but as both are using different formulas, no rule is displayed
I changed the second formula to also use "x" instead of "[x]" and tried again, now the rule was displayed and I could edit it for both fields. So that means, the CF rule editor looks for equal formulas in all selected fields and then you are able to edit them all (but you must know on your own which fields have the same formula).
when I selected B and C where only B has a formula, it's the same effect: Multiple means always: All selected fields (so you know which fields are in "multiple" because you selected them), but no rule as both controls are different.

So in the end you can say: If you select multiple fields you can only edit the rules for all of them if all formulas are identical. You can of course add a new rule in case of not identical formulas, that will be added to both. If A has the same formula as you add as new rule in case of A and C are selected (and C has no formula) then the formula will only be added to C. If you recall the CF editor you can now see the rule for A and C as they are now identical.

I would say this is a very logic solution for a CF rule editor and it is always very clear which fields are in "multiple", the ones which you selected. To be sure to see all rules which are applied to a single field you need to open the CF editor with only this field selected.

Cheers,

Christian
0
 
LVL 12

Assisted Solution

by:pdebaets
pdebaets earned 600 total points
ID: 40004440
My advice regarding Conditional Formatting would be to skip the CF editor altogether. You can set the CF using VBA. Ex.:


    Dim objFC As FormatCondition
    Const orange As Long = 62207
    Const yellow As Long = 65535
    Const blue As Long = 16769482

    Dim ctl As Control
    dim frm as Form

    '* If running from a form module
    set frm = Me
    '* If running from somewhere outside the form module
    'set frm = Forms!MyFormName

    For Each ctl In frm.Controls
        '* Process controls that have the string "Conditional" in their tag property
        If InStr(1, ctl.Tag, "Conditional") > 0 Then
            With ctl
                'Remove format conditions
                .FormatConditions.Delete
                
                    'Create format objects and add them to the FormatConditions
                    Set objFC = .FormatConditions.Add(acExpression, acEqual, _
                        "[MyField]='Value1'")
                    Set objFC = .FormatConditions.Add(acExpression, acEqual, _
                        "[MyField]='Value2'")
                    Set objFC = .FormatConditions.Add(acExpression, acEqual, _
                        "[MyField]='Value3'")
                
                'Specify the formating conditions
                .FormatConditions(0).BackColor = Orange
                .FormatConditions(0).Enabled = True
                .FormatConditions(1).BackColor = yellow
                .FormatConditions(1).Enabled = True
                .FormatConditions(2).BackColor = blue
                .FormatConditions(2).Enabled = True

            End With
        End If
    Next ctl
    Set objFC = Nothing

Open in new window

0
 

Author Closing Comment

by:Patrick O'Dea
ID: 40010377
Thanks all (espec Bitsqueezer).

Problem solved and also...
Plenty to think about ...
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question