CODEWITHSUNDEEP
vbaexcel
GregdeLima
GregdeLima

Reputation: 404

Select the column header if cell is formula

I have pieces of the code figured out, but what I can't quite get is how to parse the column and then select the first row in that column.

The objective is to color the header cell of a column, if the selected cell has a formula. The piece I'm looking for is 

Sub ColorFormulaHeaders()
Dim oWkbk As Workbook
Dim oWkst As Worksheet
Dim oRng As Range

For Each oRng In Selection.Cells
    If oRng.HasFormula Then
    'Select Column Header
         With Selection
            .Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent6
            .TintAndShade = 0
            .PatternTintAndShade = 0
         End With
    End If

End Sub

Upvotes: 2

Views: 58

Answers (1)

Gary's Student
Gary's Student

Reputation: 96763

You have the right idea. Here is one way to get to the column header:

Sub ColorFormulaHeaders()
    Dim oWkbk As Workbook
    Dim oWkst As Worksheet
    Dim oRng As Range

    For Each oRng In Selection.Cells
        If oRng.HasFormula Then
             With oRng.EntireColumn.Cells(1).Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent6
                .TintAndShade = 0
                .PatternTintAndShade = 0
             End With
        End If
    Next oRng

End Sub

You can use similar technique to get a row label for a cell in some row if the label is in column A for that cell.

Upvotes: 4

Related Questions