Conditional Formatting: Change forecolor when duplicate data.....

In access 2003...

Is there a way to format a text box in a continuous form depending on whether or not the value in the field is duplicated?

I've tried using the DCount function on the "OnCurrent" Event of the form:

If DCount("[FIELD]", "QUERY_NAME", "[FIELD] = Forms!form_name![FIELD]") > 1 THEN Me.[FIELD].ForeColor = vbRed

And I tried adding another field to the form containing the count and using that as the criteria - but then it formats for every record and not just the ones with duplicate values.


Why the hell couldn't Microsoft just include an "IN" operator in the conditional formatting tool? Does anyone know if this has changed in Access 2007?

Sponsored Links:

On a report field, I have Hide Duplicates=No AND conditional formatting on that field which depends on a value for this field. I get #NAME as result. If I remove conditional formatting or set Hide Duplicates=Yes, I get the correct data. I've found this error in other posts, but no answers. Anyone who can help is a genius!! Thanks.

I am trying to conditionally format the foreColor and backColor of several fields in a datasheet based on a combo box which has 2 values. I would like to have the entire row change colors if the combo box has a "Y" selected and vice versa.

I can get the formatting to work, however, the backColor for the field in every row is changed when I change the value of the combo box. I would like only the row that the combo box is in be updated. Can I do this with a datasheet?


Joshua Smith

When the condition is met, the back color of my text box changes. I do not want this to happen, I want the back color to stay the same, or at least match that of the form. Is there any easy way to do this? I can't find the matching color in the list of colors in the conditional formatting page. The color of the form is 'Background Light Header'.

This has got me scratching my head and I guess I could use some help please.

Working in Excel 2003, I have written a Function as follows:

	Function IsFormula(rng As Range)
 If rng.HasFormula = True Then IsFormula = True
End Function

Then I call this function as the first (of 3) critera in a conditional format as follows:


That conditional format is applied appropriately for columns A through AL and Rows 5 through 72 (e.g. =isformula(AL72))

I have buttons to which I have attached AutoFilter code. Here is an example:

	Sub ShowOpen()
 Selection.AutoFilter Field:=1, Criteria1:="Open*", Operator:=xlOr, Criteria2:="=Pending"
 SendKeys "{Down}"
End Sub

Now the head scratching bits:
If I manually apply the filter, the conditional format works fine
If I run the AutoFilter code, I get a #VALUE! error in cells where other Custom Functions are called.
HOWEVER, if I force a full calc (CTRL+ALT+SHIFT+F9) the custom functions are calculated and the conditional formats are applied.

I have stepped through the code line by line and have noticed the AutoFilter subs never actually exit the AutoFilter step. The AutoFilters are applied, but the code stops on that line. Using the above example, the "Range" and "SendKeys" lines are never reached.

If I remark out the IsFormula function, the AutoFilter buttons work fine (but the conditional formats which use the IsFormula function do not format correctly) and the other custom functions work fine.

So I came up with a formula to use in the conditional format in place of the function as follows:


Using this formula (and making sure that all references to the IsFormula Function are replaced), everything works fine. All the AutoFilters work and all the conditional formats complete and all fields are correct.

So while the issue is technically resolved, I would still like to understand why the Function method for the conditional format only works when I manually force a full calc of the sheet or manually apply the filters.
I am reasonably sure that the #VALUE! errors in the other custom functions are simply an artifact of the sort functions not properly exiting their respective Subs because all of them return the #VALUE! error AND they all work fine when the first conditional format is changed from the function to the formula.

Again, any and all help will be appreciated.

We have an excel spreadsheet that has conditional formatting on it. When a date exceeds a certain # of days, the cells change color. It works great on one computer. On other computers, the conditional formatting does not work. It is saved on the intranet within the company. What is wrong?

I have a null field in a report that I have set to zero. I have a conditional format that sets the font to red, bold. My problem is that it is not changing the zeros. All zeros will meet the conditional format change to red, bold. I am using 2000 and have checked out the help screens and searched this database also.

Any help would be appreciated.

Thanks Rich

having a right mare of a time getting some conditional formatting working based on some data exported from access

basicall the range of data is D2:AC100

D2100 contains the value i want to base the formatting on

for each cell in the rest of the range E2:AC100
If the cell is empty do nothing
If the value in the corrisponding Column D for the row is as follows do associated formatting

=1, Red Background Red Text
2 1,$D25) now i need to implement it in vba in a way that can run as a macro initiated from access

I am on the last task on my midterm and I am STUCK. In my report I have, " =Sum([ReEnrollmentFee]) " in my group footer. Well now I have to some how use conditional formatting so that when any of the values (results from the 'sum') are , " equal to 0 " it will appear in red. I just can not remember how to go about this. Help, please.

I currently have a database created that works great for what I use it for, but it was requested that I make a small change to the data access pages that i have created from this database. The pages display information from the database and what they would like is for when one of the numbers is below say 85% to conditionally format it to change to red background. I have tried to find this information and figure it out online, but so far I have not been successful.

I am attaching a sample of the database I am using. I have changed some of the information just so that I am not exposing any information from the company that may not be good to expose. The sample data access page will likely be needed to be relinked before it will work properly on anyone elses system.

Basically what I need to do is conditionally format "Efficiency" for each of the records so that when it comes up below 85% the background of the text box turns red. If it is above 85% i want the background green. This is suppose to be a fast visual indicator letting people know if things are going well or not.

Any help would be greatly appreciated.



I'm trying to do something which I have done many times in Access 2002, but I can't achieve in versions 2007 and 2010. I have a continuous subform with conditional formatting applied to every object, for the font colour to change depending on the value of one particular object. This does work at the point the subform is loaded, but if I change the value, the font colour is only changed in that one field.

For example, let's say I have three objects, one each for Fields1, 2 and 3. When I apply conditional formatting to all three, where expression is [Field1]='A' to have red font, and where [Field1]='B' to have blue font, I find that on loading the subform, all lines with 'A' in field1 will be completely in red font, and all with 'B' will be in blue. However, if I change one of the 'A's to a 'B', the font will only turn blue in Field1 for that row, and the other fields will remain red, until I either change their values, or reload the subform.

I can't find anything online about this, and as I mentioned earlier, this logic works absolutely fine in Access 2002. I tried an on change event of repaint, but that didn't work. I'd be grateful of any advice anyone can offer.



Hi. I use MS Access 2002 and I have a database with 2 forms, one in continuous view. I want to change background colors (or text colors) when a field “ActionTime” is closed to the actual time. I have studied a lot of threats here, but still I have problems to make it works.

To test the functionality, I have used the following code:

Private Sub Form_Current()
If Me.GateArea = "2" Then
Me.GateArea.BackColor = vbBlue
Me.Destination.BackColor = vbBlue
(More fields)
Me.GateArea.BackColor = vbRed
Me.Destination.BackColor = vbRed
(More fields)
End If
End Sub

This works fine in normal form view, but not in continuous view.

I know to use the conditional format, but this will only work for one field. I want to change the color of all fields on the current record based on changes on ActionTime.

Second question is how to code the data change. ActionTime is based on clock. I have used the following code, but it seems it is wrong somewhere:

If(DateDiff("n", [ActionTime]< Now()+10) Then
Me.GateArea.BackColor = vbBlue
Me.Destination.BackColor = vbBlue
(More fields)
Me.GateArea.BackColor = vbRed
Me.Destination.BackColor = vbRed
(More fields)
End If
End Sub

Thanks in advance for any help.

necessary, consult the following scripts

in cells is by using conditional formatting nastevena condition:
1 if the value is greater than 10 red cell will be
2, where the lower green

Need data to other cells when the code will write a condition in a cell "OK" if the condition of two write "NOK"

I tried using ForeColor, but somehow it did not work

I have many such cells, therefore, to try it this way, you can help me? thank

Hi all,
i'm trying to add some conditional formatting to a textbox within a form. The code works fine because I can see the effects of the conditional formats when I open the form in code. However, when I try to save it using:

DoCmd.Close acForm, FormName, acSaveYes

The changes I made in the code are not saved.

The strange thing is that if I save it manually (ie not using VBA code) while the form is still open, the conditional formatting is saved.

Also, if I add a break in the code at the end (before I save and close the form) then right-click on the textbox -> select 'Conditional Formatting...', I can see that the new conditional formats were added via vba. Then I would let the code finish. That also seems to save the changes I made using vba.

So does anyone know why I can't save the conditional formatting if I just let the code do the saving without interupting the code?

Many thanks,


The code:

Sub UpdateConditionalFormat(FormName As String, rs As DAO.Recordset, val1 As Double, val2 As Double)
Dim j As Integer
Dim i As Integer
Dim foundControl As Boolean
Dim objFrc As FormatCondition
j = 1
DoCmd.OpenForm FormName, acDesign
For i = 1 To rs.RecordCount
foundControl = False
Do While j Forms(FormName).Controls.Count And Not (foundControl) And Not rs.EOF

If Forms(FormName).Controls.Item(j - 1).Name = rs!nm_expiry Then
foundControl = True
If Forms(FormName).Controls.Item(j - 1).FormatConditions.Count = 0 Then
Forms(FormName).Controls.Item(j - 1).FormatConditions.Delete
Set objFrc = Forms(FormName).Controls.Item(j - 1).FormatConditions.Add(acFieldValue, acBetween, -val1, val1)
Set objFrc = Forms(FormName).Controls.Item(j - 1).FormatConditions.Add(acFieldValue, acBetween, -val2, val2)
Set objFrc = Forms(FormName).Controls.Item(j - 1).FormatConditions.Add(acFieldValue, acNotBetween, -val2, val2)

With Forms(FormName).Controls.Item(j - 1).FormatConditions(0)
.BackColor = RGB(189, 252, 201)
.Enabled = True
End With
With Forms(FormName).Controls.Item(j - 1).FormatConditions(1)
.BackColor = RGB(255, 246, 143)
.Enabled = True
End With
With Forms(FormName).Controls.Item(j - 1).FormatConditions(2)
.BackColor = RGB(255, 153, 18)
.Enabled = True
End With

End If
End If

j = j + 1


DoCmd.Close acForm, FormName, acSaveYes

In one of my forms I have used this code in the properties of a check box (with the names changed) to apply conditional formatting to a text box when the checkbox is ticked:

Private Sub Check14_AfterUpdate()
If Me!Check14 = True Then
Me!txtDesc.BackColor = vbRed
Me!txtDesc.ForeColor = vbBlack
Me!txtDesc.BackColor = vbWhite
Me!txtDesc.ForeColor = vbRed
End If
End Sub

It works perfectly, except that when I apply a change to one record the same colour change is applied to all records in the form regardless of whether or not the checkbox has been ticked. What am I missing?

I hope that someone out here can help me with this. I have recently had a format change from how I received updated data for my database. I used to update my tables from TXT files. There were several update queries/macros written to import the data from 5 or 6 different TXT files into one table. Well; my source has changed The downside: I can only get my data from an XLS or CSV file now; but the upside is all the data is only in 1 file.

The macros/queries were written years ago and I've been having migranes trying to figure out how this was all done. So, i've sort of started from scratch. I can easily import the data into a table; I have even gotten our front end of the database working so it's just a click of the mouse again.

The problem I have is that the data that is in this table is linked everywhere throughout this spiderweb of a database! I was unable to import my data into the existing table because that table's field names are different than what I have in the XLS file. I get all sorts of errors when I do this. So I am currently importing into a new table. (I.E. MASS and MASS Imports)

Is there an easy way to update data from table 'MASS Imports' to 'MASS' ?? what I was thinking was to use an update query with both tables and link each line.... Or am I way off base? Is there an easier way to do this?

Thank You!!

I have a continuous form bound to a table containing a number of records pertaining to water quality sampling. One of the fields, Activity_category has a conditional formatting condition that counts records in the table that are 'duplicates' with regard to 4 of the fields in the table. (differences in other fields make no difference in regarding whether a record is a 'duplicate')

The conditional formatting works perfectly in one version of the form (during data entry) but does not work in another version of the same form (that is based on the same table, and that differs mainly by having different on_close events and some additional command buttons. 9The second form was created by copying and pasting the original, then making additions of command buttons etc).

The condition statement is the same in both forms but the formatting does not appear at all in the second version of the form even when records that meet the condition are in the table (see attached for example records).

The condition statement for the control on both forms is an expression as follows:

	[ParameterID] Is Not Null And DCount("[ResultID]","tblSampleData","[ParameterID]=" & [ParameterID] & " AND [SiteVisitID]=" &
[SiteVisitID] & " AND [SampleTaken]=-1 AND [SampleStrata]=" & [SampleStrata])>1

My only clue might be something to do with the second record in the duplicate record pair being entered after other data has been entered for other sites etc. Is it possible that the DCount stops counting for some reason once it encounters a record with a higher SiteVisitID number? (SiteVisitID is a foreign key from a different table). I thought DCount was supposed to count every record in the table meeting the criteria specified?

I have attached a copy of some records from the table in question in case I'm missing something obvious.

The two records that should trigger the condition are highlighted in blue. The four fields used as criteria in the condition have their field name highlighted in green.

Several records in between the two 'duplicate' records have been 'hidden' for the purpose of clarity. The TRUE/FALSE values are output from Yes/No fields so True represents a -1, and False represents a 0.

And to complicate matters further, if I view the form and randomly click in the activity_category control, then in another control on another record, /sometimes/ one of the two duplicates will highlight blue as they are supposed to. But after a lot of trying, I can't determine any pattern to when it might work and when it will just stay yellow (default).

Anyone else encounter something like this?

Hey guys i'm kind of confused. I'm new to access but I've been getting a lot of progress.

I have a form FRMDATABASE that is loaded into FRMSEARCH and frm search has a bunch of txtboxes that searches the the FRMDATABASE but now I need to conditional format the data within the FRMDATABASE and I have to do it based of code sense I can't limit it to 3.

I use this on the report of the searched criteria and it works like a charm:

If [Description] Like "*" & "Print Not Available" & "*" Then
[Description].BackColor = &H51A8FF
[Description].FontBold = True
[Description].ForeColor = vbBlack
ElseIf [Description] Like "*" & "In Production" & "*" Then
[Description].BackColor = vbYellow
[Description].FontBold = True
[Description].ForeColor = vbBlack
[Description].BackColor = vbWhite
[Description].FontBold = False
[Description].ForeColor = vbBlack
End If

If [General Notes] = "*" & "Ready to Ship on Rack" & "*" Then
[General Notes].BackColor = &HA8ACFF
[General Notes].FontBold = True
[General Notes].ForeColor = vbBlack
ElseIf [General Notes] Like "*" & "Need Test Sheet" & "*" Then
[General Notes].BackColor = &HFFFF00
[General Notes].FontBold = True
[General Notes].ForeColor = vbBlack
ElseIf [General Notes] Like "*" & "Error" & "*" Then
[General Notes].BackColor = vbRed
[General Notes].FontBold = True
[General Notes].ForeColor = vbWhite
ElseIf [General Notes] Like "*" & "Order on Hold" & "*" Then
[General Notes].BackColor = vbRed
[General Notes].FontBold = True
[General Notes].ForeColor = vbWhite
[General Notes].BackColor = vbWhite
[General Notes].FontBold = False
[General Notes].ForeColor = vbBlack
End If

I tried making this on the frmSearch under Public ConditionalFormat()

With all the threads hanging about regarding the limitations of Access with conditional formatting. Ie being limited to 3+1 colours. I thought is was about time someone did something to expand the colour range.

In this simple demo I have created one table and one form. In the table there is a field that is an OLE field. You could extend this to two tables one with the ole field and a PK and your main table with a FK pointing to it. But for this example I have just used one.

The ole field contains a simple bitmap of a plain colour, Red, Green, Blue, Yellow, etc. As the developer you will determine the number and colour of the bitmaps. The form is a simple continuous form with all four fields in it. The data item fields (Make, Model & Oil change date) are listed left to right and have transparant backgrounds and borders. The field that contains the bitmap is sat behind all three (Sent to Back). When you view the form it appears that the row is coloured according to the bitmap colour.

How to employ this functionality.

Lets say you have a status for a particular record, this may be Active, Inactive, dormant, awaiting activation, under review, etc, etc. As the developer you have a colour key to indicate each status. By assiging the colur to the record you can display the record with the correct colour.

As an afterthought, if I had used a 1-M table I could have used the FK to create filters based on the colour code. Obviously this colour coding is at record level and not at field level. However, lets say you had three fields that were mission critical and you wanted to have conditional formatting on each field. Then simply create three ole fields (one for each). Set the colour for each appropriately and drop the field behind the field as per above.

Hope this is useful to so members and look forward to any feedback.


I have taken a look around for this, but all I've found, so far, are examples for dates and date ranges. I want to use conditional formatting to change the background colour of a date field when no date has been entered.

How do I specify if the field data for a date field has no value? I have tried Null and 0, and a few other things, but I can't seem to get this to work.