Change Colour if meets a condition

[Sharon Hague]

Hi All

I have a report, based on a query which is run weekly. It shows all the days for that week on the left for that employee and their attendance for each day from 8.30 am through to 5 pm.

If that employee if i.e on holiday or sick for that date it displays "H" in the text box or "S" etc.

Is it possible for the H to be printed in i.e red and the S to be printed in blue etc.

Please advise

 

[IMO]

This should do it...

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


If Me.YourTextBox = H Then
Me.YourTextBox.FontBold = True
Me.YourTextBox .ForeColor = vbRed
Else
Me.YourTextBox .FontBold = False
Me.YourTextBox .ForeColor = vbBlack
End If

If Me.YourTextBox = S Then
Me.YourTextBox.FontBold = True
Me.YourTextBox .ForeColor = vbBlue
Else
Me.YourTextBox .FontBold = False
Me.YourTextBox .ForeColor = vbBlack
End If

End Sub

IMO

 

[IMO]

Sorry, ignore the last post, use this code instead...

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

If Me.YourTextBox = "H" Then
      Me.YourTextBox.FontBold = True
      Me.YourTextBox.ForeColor = vbRed
Else
      Me.YourTextBox.FontBold = False
      Me.YourTextBox.ForeColor = vbBlack
End If

If Me.YourTextBox = "S" Then
      Me.YourTextBox.FontBold = True
      Me.YourTextBox.ForeColor = vbBlue
End If

End Sub

IMO

 

[Sharon Hague]

IMO

Thanks for your reply but it doesn't work.

I have added your coding to my detail part of the report and the text box name is called test, therefore my coding reads: -

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

If Me.test = "H" Then
Me.test.FontBold = True
Me.test.ForeColor = vbRed
Else
Me.test.FontBold = False
Me.test.ForeColor = vbBlack
End If

If Me.test = "S" Then
Me.test.FontBold = True
Me.test.ForeColor = vbBlue
Else
Me.test.FontBold = False
Me.test.ForeColor = vbBlack
End If

End Sub

I had to place " " around H or S otherwise it came up with an error.

Do I need to change anything else?

 

[IMO]

My second post should do the trick

IMO

 

[Sharon Hague]

IMO

Brill! - It's worked a treat

Thanks

 

[IMO]

You're welcome

IMO

 

[ColinEssex]

I take it your PM to me is now not needed Sharon?

Col

 

[Sharon Hague]

Hi Colin

I didn't think it would work in the same way which is why I posted you.

I can't get it to work with the form with a subform in the same way with the coding so I would appreciate it if you could advise.

Cheers

 

[IMO]

You can place the same code in the Forms OnCurrent event and in the Subforms OnCurrent event, but, the Subforms Default View cannot be set to Datasheet or it will not work.

IMO

 

[Sharon Hague]

Thanks IMO

I will change my subform from datasheet.

Thanks