CODEWITHSUNDEEP
ms-accessvba
Nexus
Nexus

Reputation: 821

How to change color for part of the text in a particular field in Access?

In Access, I have a table which contain a field like this:

Part Number

A/B/C

A/B/R

T/Y/V

D/A/I

I want to change the color of the all the third part to red. So in this case C,R,V,I will be colored red. But I can't do change the color of part of the text field in Access 2007. If I use Font Change under Home tab it change the Font of entire Table. I'm so disappointed about Microsoft. Is there any way to change the color would be great :D You can recommend VBA , Macro in Form, Query, Report ... P/S: I use Access 2007

Upvotes: 1

Views: 8880

Answers (2)

Glen Little
Glen Little

Reputation: 7128

if you can use an Access report, you can add a TextBox to the report. In the textbox, you can have a formula like this: 

="<font color=""blue"">" & [ColumnA] & "</font> <u>" & [ColumnB] & "</u>"

See Access Rich-Text: Which subset of HTML is supported? for more details.

Upvotes: 3

Nexus
Nexus

Reputation: 821

ok I think the only way is to export automatically to Excel. Finally I can do this

Private Sub CommandExport_Click()
Dim db As Database
Dim rec1 As Recordset
Dim xlFile As Object
Dim xlWorkBook As Object
Dim xlActiveWkb As Object
Dim xlActiveSheet As Object
Dim iCols, iRows, flag As Integer

Set db = CurrentDb
Set xlFile = CreateObject("Excel.Application")
Set xlWorkBook = xlFile.Workbooks.Add
Set xlActiveWkb = xlFile.Application.ActiveWorkBook

xlFile.Visible = True
xlActiveWkb.Sheets.Add
xlActiveWkb.Worksheets(1).Name = "My_Report"

Set xlActiveSheet = xlActiveWkb.Worksheets("My_Report")
Set rec1 = db.OpenRecordset("Report")

For iCols = 0 To rec1.Fields.Count - 1
    xlActiveSheet.Cells(1, iCols + 1).Value = rec1.Fields(iCols).Name
    If rec1.Fields(iCols).Name = "FS Number" Then
        flag = iCols
    End If
Next

xlActiveSheet.Range(xlActiveSheet.Cells(1, 1), xlActiveSheet.Cells(1, rec1.Fields.Count)).Font.Bold = True
xlActiveSheet.Range(xlActiveSheet.Cells(1, 1), xlActiveSheet.Cells(1, rec1.Fields.Count)).Interior.ColorIndex = 15
xlActiveSheet.Cells(2, 1).CopyFromRecordset rec1
xlActiveSheet.Columns("A:AD").EntireColumn.AutoFit

iRows = 1
rec1.MoveFirst
While Not rec1.EOF
    xlActiveSheet.Cells(iRows + 1, flag + 1).Characters(InStr(rec1![FS Number], "*")).Font.ColorIndex = 3
    iRows = iRows + 1
    rec1.MoveNext
Wend

Set xlSheet = Nothing
Set xlWorkBook = Nothing
Set xlActiveWkb = Nothing
rec1.Close
db.Close
Set rec1 = Nothing
Set db = Nothing

End Sub

The magic is here 

xlActiveSheet.Cells(iRows + 1, flag + 1).Characters(InStr(rec1![FS Number], "*")).Font.ColorIndex = 3

Upvotes: 0

Related Questions