?
Solved

Access Datasheet "Conditional Formatting" on multiple cells

Posted on 2014-04-15
4
Medium Priority
?
1,936 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 51

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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…
Suggested Courses

765 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