-2

everyone. I working on a project in Excel/VBA and have encountered some problem. So, Set wsMain = ThisWorkbook.Sheets(3) is the worksheet I need the coloring and I currently have 

With wsMain
    .Columns("A:AO").AutoFit
    '.Range("A1:AO1").Columns.AutoFit
    .Cells.ClearFormats
    .Rows(1).Font.Bold = True
    .Cells.Font.Color = RGB(0, 0, 255)

The only thing I need to do is color every cell in the worksheet below the first row. The first row is freezed and should not have any background color.

Can anyone suggest a good approach to solve this problem?

Thanks,

Community
  • 1
  • 1
BLkrn
  • 77
  • 2
  • 14
  • Please see *[this](http://stackoverflow.com/help/how-to-ask)* on how to ask a good question and edit your question accordingly. This is not a code writing service, rather a site to seek a specific answer to a specific programming question. – 314UnreadEmails Jun 01 '15 at 19:37
  • I just edited the question. – BLkrn Jun 01 '15 at 19:56

1 Answers1

0

You can use Resize() and Offset() to accomplish this:

With wsMain
    .Columns("A:AO").AutoFit
    '.Range("A1:AO1").Columns.AutoFit
    .Cells.ClearFormats
    .Rows(1).Font.Bold = True
    .Cells.Resize(wsMain.Rows.Count - 1).Offset(1).Font.Color = RGB(0, 0, 255)

Resize resizes the range to the number of Rows (and optionally Columns) that you specify, so we just get the number of rows in the worksheet and subtract one. Then we use Offset to move the range one row down the sheet.

JNevill
  • 46,980
  • 4
  • 38
  • 63
  • Thank you so much! Now the problem is when I color the cells, the borders disappear and I need to include in the code that ensure that borders will show after the coloring. Can you help me with that please? – BLkrn Jun 01 '15 at 20:30
  • .Rows(1).Font.Bold = True .Cells.Font.Name = "Georgia" .Cells.Font.Color = RGB(0, 0, 225) .Cells.Resize(wsMain.Rows.Count - 1).Offset(1).Interior.ColorIndex = RGB(216, 228, 188) doesn't seem to work though .. – BLkrn Jun 01 '15 at 20:54
  • That should make the first row Bold. All cells set to "Georgia" font with a blue color. And everything but the first row should be colored a really odd light shade of green. Are you getting something else from that code? Are there any errors? – JNevill Jun 01 '15 at 20:58