Conditional formatting code issue

[vipa2000]

i have tried to piece together some code which will change the colour of a week number dependent on certain parameters. I am not a programmer, so please if it is poor,allow for the newbies on town.

Here goes. I have two cells in a report one called 'year' and one called 'weekno'. i want the code to check todays date and validate it against the 'year' and 'weekno'. If equal to or less than todays week number then change the cell colour to red. If one week on then change to blue, two weeks on green and then anything beyond that to a another colour. This is what i have done so far, but don't know how to piece the year element in. Uptil year end i would be okay but come 2007 the week number would revert to 1. thanks in adavance.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Select Case CInt(txtWeek)

Case CInt(Format(Now(), "ww"))

Me.Section(acDetail).BackColor = vbRed

CaseCInt (Format(Now(), "ww")) + 1

Me.Section(acDetail).BackColor = vbBlue

CaseCInt (Format(Now(), "ww")) + 2

Me.Section(acDetail).BackColor = vbGreen

End Select

End Sub

 

[KenHigg]

Are you trying to do this where you have multple records from the same table in the report? If so, you may need to use conditional formatting:

http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=12151&d=1136824685

 

[vipa2000]

Hi Ken, I am using access 2000. Attachement will not open. Conditional formatting will not work if it is the inbuilt function. Maximum of 3 allowed and it does not work properly for some reason.

Can you send a 2000 version if you have used code. I am basing this on multiple records.

regards

vipa

 

[KenHigg]

I don't know that you can do this with code. I'm thinking that when you set/reset a property it will change the entire column...