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?

Post your answer or comment

comments powered by Disqus
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.

Hello everyone. This is my first post after being a lurker for a number of years - you guys have taught me a huge amount, so huge thanks everyone

Now I have an issue that I can't seem to find a solution on here for and would like some advice - apologies if this question has already been answered/solved and I just didn't find it, if so if someone would be so kind as to point me at the thread I'd be grateful.

I'm trying to build a calendar-like form to show where staff are in the current month (it's a personnel database) - I.E if they're on holiday or off sick. I have a continuous form that contains 32 text boxes in the "Detail" section, and the maximum number of rows is around 10 (with the current data). The first text box is a bound name column, and the next 31 text boxes are unbound with a function I wrote to figure out where a person is for every day in the current month, which updates each text box to one of 4 values for each "day"; Null if the date is invalid (such as the text box for the 31st in November) or a weekend day, 1 if a record is found for the staff member in the holiday table, 2 if a record is found for the staff member in the sickness table, and 0 if no record is found in either table.

When I load the form, it takes between 21 and 23 seconds to load and calculate all the values (not amazing performance, but acceptable).

What I want is for all of the boxes to be blank where the values are 0, blue where the value is 1, and red where the value is 2. (Ignore weekend and invalid dates for now, I'm handling them separately - though I don't think this is causing my problem because I haven't written it in yet). To do this, I added simple conditional formatting to the 31 text boxes to change the font and fill colour to the relevant colours based on the text box value, then set the default value to a white fore colour and back colour.

My problem is that when I do this, the form takes unacceptably long to load (I stopped timing after 3 and a half minutes and crashed it out). Stranger still, after the first 20-30 seconds what I can see on the form is that it has already calculated all the values and coloured them appropriately but the VB code window still appears to be running through the code to calculate the day values - but all I've changed is to add conditional formatting! If I remove the conditional formatting, the form reverts back to only taking 20-ish seconds to load.

If anyone could help me figure out why adding CF to the form seems to be causing this I'd be very grateful.

Oh, I'm using Access 2003 by the way.

Hey guys, I'm new here so a big HELLO to all who are reading this. I'm really glad to be here as there is so much I want to learn.

I wonder if anyone can help me with this?

I have 7 subforms, each containing 30 textboxes. Each field represents a half hour slot, in a 15 hour day. The fields are populated in two ways: 1. from linked tables from an online booking system and 2. user input on the forms. There are 3 field values that are constant on the website and when this data is pulled into the form I want to change the backcolor of the textbox. Now, as these entries are always for one hour but our forms need to show half hour slots, to allow for user entries, I need to change the backcolor of the textbox being evaluated plus the next one in line, to indicate the hour is booked.

I can do this of course with conditional formatting, but that would mean adding in approx. 400 rule expressions. Unless there is a way of referring to a textbox control without having to give its name? And then the following half hour field's textbox?

I tried looping through the controls but again couldn't figure out a way of referencing the next one in line.

Any help would be fantastic


I've got a report I made that sums up hours and totals costs, grouped by invoice. This works just fine.
I wanted to add a conditional formatting field that would change the fore-color of the Date Received if it was after the due date. This also works, however, the alternating row color seems to get confused and switches to the opposite of what it should be.
I can't figure out why this is happening. It doesn't happen when conditional formatting isn't being used. Attached Thumbnails   Reply With Quote 11-02-2011, 02:15 AM #2 June7 Super Moderator Windows XP Access 2010 32bit Join Date May 2011 Location The Great Land Posts 15,129 I just tested this and cannot replicate your issue. Maybe has something to do with 64bit version.

If you want to provide project, will give it a try.

Can someone help me out with these IIf statements below? I need the days to be >= and

Hi All,

I have an access database which has a subform that holds dates of community groups such as the insurance renewal date. I would like to have a message displayed on the main form when this date has expired using conditionlal formating to change the colour of a text box with a default value.

Then once a new insurance date is entered (on a seperate field) it will ignore the old one and use the new date for the condition formatting.

Does anyone know if this is possible?


Not finding an answer? Try a Google search.