CODEWITHSUNDEEP
excelvba
Baber62
Baber62

Reputation: 53

vba to color font in adjacent columns

In this particular spreadsheet in column E from cell E6 onwards I have the values 0 or 1, having converted a number to binary. In the adjacent cells I have values i.e. in cells F6 and G6. If the value is 0 in column E I want the font in columns F and G to be grey color RGB(192,192,192), if the value is 1 in column E then I want the font to be made bold in columns F and G. I need to do this programmatically as I am trying to achieve this on a change event.

Upvotes: 0

Views: 514

Answers (1)

Soulfire
Soulfire

Reputation: 4296

If you want the coloring to happen anytime something is changed on the sheet, we need to look at the Worksheet_Change event.

Go to the VB Editor by pressing ALT+F11 or, under the Developer tab, select Visual Basic.

In the editor, right click the worksheet that you want this formatting to occur on and then select View Code.

Paste the collowing code in:

Private Sub Worksheet_Change(ByVal Target As Range)

        Dim lRow As Long
        Dim c As Range

        'Get the last used row in column E
        lRow = Me.Cells(Rows.Count, "E").End(xlUp).Row

        'Go through column E and check values
        For Each c In Range("E1:E" & lRow)
                If c.Value = 0 Then
                        'Offsets to column F, G and colors font
                        'if the value in column E is 0
                        c.Offset(0, 1).Font.Color = RGB(192, 192, 192)
                        c.Offset(0, 2).Font.Color = RGB(192, 192, 192)
                End If
        Next c

End Sub

Upvotes: 4

Related Questions