Format borders for rows where date is < another date

[cnstarz]

On my report, I want rows with expired dates to stand out by having their borders thicker. Something like:

If Me.txtDate < Date() Then
     Me.txtDate.BorderWidth = 6
End If

When I do that, it ends up formatting every txtDate field in the report if one of them passes the If criteria instead of only ones that are expired.

Any help guys?

 

[vbaInet]

You can do that in the Format event of the section that textbox is in:

If FormatCount <> 1 Then Exit Sub

With Me.txtDate
    If .Value < Date() Then
        .BorderWidth = 6
    Else
        .BorderWidth = 0
    End If
End If

However, if you're going to use Report View it's not going to work. If you are let me know and I'll tell you of another technique.

 

[cnstarz]

You can do that in the Format event of the section that textbox is in:

If FormatCount <> 1 Then Exit Sub

With Me.txtDate
    If .Value < Date() Then
        .BorderWidth = 6
    Else
        .BorderWidth = 0
    End If
End If

However, if you're going to use Report View it's not going to work. If you are let me know and I'll tell you of another technique.

Yeah the report autorefreshes every 15 seconds and will be displayed up on a few monitors for situational awareness. What's the other technique?

 

[vbaInet]

You use Conditional Formatting to change the background colour of a textbox that sits behind your textbox. Ensure that background style of txtDate is solid (and not transparent).

 

[cnstarz]

I already know that lol. Unfortunately my leadership wants the borders changed for affected records, not the background color.

 

[vbaInet]

You misunderstand what I'm telling you.

1. Two textboxes, one is txtDate and the other sits behind txtDate. I'll call it txtBack
2. Set the back colour of txtBack to Black and move it so it sits and looks like a thickened border of txtDate
3. Use Conditional Formatting on txtBack to change its back colour to White if it doesn't match the criteria otherwise it will be be black.

 

[cnstarz]

You misunderstand what I'm telling you.

1. Two textboxes, one is txtDate and the other sits behind txtDate. I'll call it txtBack
2. Set the back colour of txtBack to Black and move it so it sits and looks like a thickened border of txtDate
3. Use Conditional Formatting on txtBack to change its back colour to White if it doesn't match the criteria otherwise it will be be black.

Touche, my apologies! I will look into this. It's too bad MS didn't think to add something as simple as borders to conditional formatting. :banghead:

 

[vbaInet]

We'll be waiting!