Solved

Access Datasheet "Conditional Formatting" on multiple cells

Posted on 2014-04-15
4
1,612 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
4 Comments
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 50 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 300 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 150 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now