Changing font color in conditional formatting

Question

I am trying to change the color of a range of cells ("A" to "N") depending on the value of a defined cell, in this case the value in cell "H". I need to change to white and bold for the cases "credentialing", "ci error/ticket"and "completed/backup" and the other cases keep it in regular black.

I have the code for changing the cell color on the defined range but I don't know how to apply the code for the font style and color to change. Here is what I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Columns("H")) Is Nothing Then
        On Error GoTo bm_Safe_Exit
        Application.EnableEvents = False
        Dim trgt As Range
        For Each trgt In Intersect(Target, Columns("H"))
            Select Case LCase(trgt.Value2)
            Case "2 day process"
                    Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 46
                    Case "advisor"
                        Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 37
                    Case "back in"
                        Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 22
                    Case "ci error/ticket"
                        Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 1
                    Case "completed"
                        Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 10
                    Case "completed/backup"
                        Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 51
                    Case "credentialing"
                        Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 49
                    Case "credit"
                        Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 44
                    Case "duplicate"
                        Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 10
                    Case "held"
                        Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 37
                    Case "master data"
                        Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 37
                    Case "name change"
                        Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 37
                    Case "ofr"
                        Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 3
                    Case "op consultant"
                        Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 37
                    Case "post process"
                        Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 32
                    Case "pps"
                        Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 37
                    Case "react acct"
                        Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 37
                    Case "rejected"
                        Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 10
                    Case "transferred"
                        Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 10
                    Case "zpnd"
                        Cells(trgt.Row, "A").Resize(1, 14).Interior.ColorIndex = 37
                    Case Else
                        Cells(trgt.Row, "A").Resize(1, 14).Interior.Pattern = xlNone
            End Select
        Next trgt
    End If
bm_Safe_Exit:
    Application.EnableEvents = True
    End Sub

msdn.microsoft.com/en-gb/library/office/ff838238.aspx & msdn.microsoft.com/en-gb/library/office/ff196273.aspx should help you. — Benno Grimm
– Benno Grimm, Commented Jul 15, 2016 at 5:38

Fratyx · Accepted Answer · 2016-07-15 05:10:26Z

1

You have to access the Font property of the cells. So in your case

Cells(trgt.Row, "A").Resize(1, 14).Font.ColorIndex = 1
Cells(trgt.Row, "A").Resize(1, 14).Font.FontStyle = "Bold"

answered Jul 15, 2016 at 5:10

Fratyx

5,8271 gold badge16 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rogiefc Over a year ago

Thank you for your response. I applied the code to my worksheet on the cases listed on my question but is not changing the font properties on the desired range of cells. I tried something similar and it was changing the color, the problem was that when I changed the value or removed the value on cell "H" the font was still white and I need it to change to default

Collectives™ on Stack Overflow

Changing font color in conditional formatting

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related