Color Header Specific Color Only to End of Text

[MSmithson]

I have a header (row 1) in my worksheets that I need to fill with a specific color (ColorIndex 13 – purple color), but only in the cells in that row that have text. For example, sometimes the header will go to Col H, sometimes to Col K, etc. Trying to write a macro that will only fill the row with color with cells that contain text.

Help is much appreciated, thanks!

[Leith Ross]

Hello MSmithson,

This will color only the cells in row 1 that have text.

Sub ColorHeader()

  Dim Cell As Range
  
    For Each Cell In Range("A1", Cells(1, Columns.Count).End(xlToLeft))
      If Cell <> "" Then Cell.Interior.ColorIndex = 13
    Next Cell

End Sub

[Marcol]

Couldn't you use Conditional Formatting rather than a macro?

Select Row 1
Conditional Formatting > Use a formula ...

Formula:-

=ISTEXT(A$1)

Apply your format colours to fill and font as required.

ColorIndex 13 – purple is rather dark, you might want to make font formatting
colour:= white.

Hope this helps