Formatting Column Headings

[jeremiah]

Currently using 4 rows for my column headings, my issue is that some of the
column headings have text in row 1 and 3 and no text in row 2 and 4, other
headings have text in all 4 rows. I cannot seem to merge the cells into 1
because that doesn't give me the white space between rows where I need it. I
am trying to condense all of the values into 1 row so when I do my
xlFilterCopy to new sheets, it will copy the header row on each sheet. Or is
there a way to copy 4 header rows to each sheet? or Copy 4 header rows
between each change in value and then filtering to each sheet? Am stumped
for what would be the best solution.
GOALS 1,111 WPH
Work Rates
Week Ending: Work Work
Volume Hours

 

[Joel]

For ColCount = 1 to 20
Data = cells(1,ColCount) & vbcrlf
Data = Data & cells(2,ColCount) & vbcrlf
Data = Data & cells(3,ColCount) & vbcrlf
Data = Data & cells(4,ColCount)
cells(1,ColCount) = Data
next Colcount
rows(2:4").delete

 

[jeremiah]

Got this adjusted to work, thanks so much, but have introduced a new dilemma
- row 1 in my file needs to be filled with black, and white font while row 3
- 4 are standard fill and text color. Is there a way to split the colors of
the cell now that it is merged?

 

[Joel]

I should of used vblf instead of vbcrlf. you can't change the background
colors but can change the font.

For Colcount = 1 To 20
Data = Cells(1, Colcount) & vbLf
Data = Data & Cells(2, Colcount) & vbLf
'get position of 1st character of third line
Start3rdLine = Len(Data) + 1
Data = Data & Cells(3, Colcount) & vbLf
Data = Data & Cells(4, Colcount)
Cells(1, Colcount) = Data
LastChar = Len(Data)
Cells(1, Colcount).Characters(Start3rdLine, LastChar).Font.ColorIndex = 5
Next Colcount
Rows("2:4").Delete