Colour top row (header row) regardless of number of columns-CodePudding

I'm new to VBA and thus this might be an easy one. I'm trying to colour the top row of any workbook regardless of the amount or columns. I would obviously like the colour to stop with the last column.

The colour isn't too important as I can change it in the code once figured out.

CodePudding user response：

The macro recorder can help us a lot to start going with vba.

I have recorded a macro were I have selecetd the top row of a worksheet, and changed its color. This is what i've got

Sub Color()
'
' Color Macro
'

'
    Rows("1:1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 10092441
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Now there are some definitions here that might or may not interest you.

A simpler code to have the row gray using RGB:

Sub Color()
'
' Color Macro
'
    
    With Rows("1:1").Interior
        .Color = RGB(215, 215, 215)

    End With
End Sub

CodePudding user response：

Let me give you a surprising proposal:

Rows("1:1").FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=NOT(ISBLANK(A1))"
Rows("1:1").FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Rows("1:1").FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent6
    .TintAndShade = 0
End With

Rows("1:1").FormatConditions(1).StopIfTrue = False

(This is more or less the result of the macro recorder, some things might be obsolete.)

In order to obtain this, I used conditional formatting, based on the formula "=NOT(ISBLANK(A1))". In other words: when something gets written in that cell, the cell gets a background colour.

I've used this approach as you mentioned "regardless of the number of columns". The other answers start with a certain amount of columns but if you add one, you need to run their macro again. My solution does not need that.