FormatConditions (1 Viewer)

asid

Registered User.
Local time
Today, 21:18
Joined
Jan 25, 2006
Messages
22
Hi
I am having issues with dates in conditional formatting.
This line is not formatting all rows to red if the field [ETC] is less than today's date. But some rows are returned red, what tells me it is not passing as a date but string. (see attached)

c.FormatConditions.Add acExpression, acEqual, "[ETC] < '" & DATE & "'"

The lines remarked out do work as an example of checking a date is null.

Private Sub Form_Load()
Dim lngRed As Long
Dim lngWhite As Long
Dim lngBlack As Long
Dim lngYellow As Long
Dim lngGreen As Long
Dim lngBlue As Long
Dim lngBrown As Long
Dim lngPink As Long

Dim c As Control
Dim t As TextBox

lngRed = RGB(255, 0, 0)
lngWhite = RGB(255, 255, 255)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngGreen = RGB(0, 153, 76)
lngBlue = RGB(0, 0, 255)
lngBrown = RGB(153, 76, 0)
lngPink = RGB(255, 0, 255)


For Each c In Me.Controls

If (TypeOf c Is TextBox) Or (TypeOf c Is ComboBox) Then

c.FormatConditions.Delete
c.FormatConditions.Add acExpression, acEqual, "[ETC] < '" & DATE & "'"
'c.FormatConditions.Add acExpression, acEqual, "[DateSend] Is Null"


c.FormatConditions(0).Enabled = True
'c.FormatConditions(1).Enabled = True

c.FormatConditions(1).ForeColor = lngRed
'c.FormatConditions(0).ForeColor = lngbLUE

End If
Next

End Sub

Thanks :)
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:18
Joined
Aug 30, 2003
Messages
36,118
Try # as the delimiter rather than ', which is the text delimiter.
 

sneuberg

AWF VIP
Local time
Today, 01:18
Joined
Oct 17, 2014
Messages
3,506
If [ETC] is a Date/Time field I don't think you want any limiters at all. The following screen shot shows how you can enter this rule. Why don't you just add these rules through the ribbon rather than putting it in the Form Load?

 

Attachments

  • ConditionalFormating.jpg
    ConditionalFormating.jpg
    74.5 KB · Views: 538

asid

Registered User.
Local time
Today, 21:18
Joined
Jan 25, 2006
Messages
22
There is bout 30 fields so better in code

I tried this but still returns odd results
c.FormatConditions.Add acExpression, acEqual, "[ETC] < #" & DATE & "#"

This does not work, returns an expression of 0
c.FormatConditions.Add acExpression, acEqual, [ETC] < DATE

?
 

sneuberg

AWF VIP
Local time
Today, 01:18
Joined
Oct 17, 2014
Messages
3,506
I think the expression needs to be in quotes. I suggest trying:
Code:
c.FormatConditions.Add acExpression, acEqual, "[ETC] < Date()"

Also this might help.
 

sneuberg

AWF VIP
Local time
Today, 01:18
Joined
Oct 17, 2014
Messages
3,506
There is bout 30 fields so better in code

If the 30 field are to receive the same formatting you can just selected them all (shift click on each) the the conditional format will apply to all of them.
 

asid

Registered User.
Local time
Today, 21:18
Joined
Jan 25, 2006
Messages
22
Hi

Found the solution as follows, thanks for you help

dim xDate as Date

xDate = Format([ETC], "dd/mm/yyyy")
c.FormatConditions.Add acExpression, acEqual, xDate < DATE
 

Users who are viewing this thread

Top Bottom