Fill range in first empty row

[Ironman]

Hi

I have got so far with the below code, which fills Column A in the first empty row

Sub FillEndRowBlue()

If ActiveSheet.Name = "Training Log" Then
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
With Selection
.Interior.Color = RGB(197, 217, 241)

End With
Else
MsgBox "Cell fill does not work in this sheet", vbInformation, "Information"
End If
End Sub

I'm trying to fill columns A:F and I:J in the first empty row and I'm stuck!

Help would be much appreciated.

Many thanks!

 

[Peter_SSs]

I'm trying to fill columns A:F and I:J in the first empty row and I'm stuck!

Like this?

Sub FillEndRowBlue()
  If ActiveSheet.Name = "Training Log" Then
    Intersect(Rows(Cells(Rows.Count, 1).End(xlUp).Row + 1), Union(Columns("A:F"), Columns("I:J"))).Interior.Color = RGB(197, 217, 241)
  Else
    MsgBox "Cell fill does not work in this sheet", vbInformation, "Information"
  End If
End Sub

 

[Ironman]

Hey Peter, that works perfectly and made me smile, thank you!

Best regards

Paul

 

[Peter_SSs]

Cheers.

 

[Ironman]

Hi

Would it be possible to add a bit more code so column 2 of the first empty row contains the text "OTHER" and column 9 contains the text "Indoor bike session, 60 mins." (without the commas)?

Many thanks!

 

[Peter_SSs]

Like this?

Sub FillEndRowBlue_v2()
  If ActiveSheet.Name = "Training Log" Then
    With Intersect(Rows(Cells(Rows.Count, 1).End(xlUp).Row + 1), Union(Columns("A:F"), Columns("I:J")))
      .Interior.Color = RGB(197, 217, 241)
      .Cells(2).Value = "OTHER"
      .Areas(2).Cells(1).Value = "Indoor bike session, 60 mins."
    End With
  Else
    MsgBox "Cell fill does not work in this sheet", vbInformation, "Information"
  End If
End Sub

 

[Ironman]

WOW, thank you ever so much Peter, that's brilliant!

Looking at your solution, which part identifies the 2 columns, so I know how to apply this in other sheets/columns?

Thanks again!

 

[Peter_SSs]

The range being coloured is in two 'areas', columns A:F and columns I:J

.Cells(2).Value = "OTHER"
This does not specifically identify which area so it defaults to the first area and the second cell in the first area is column B

.Areas(2).Cells(1).Value = "Indoor bike session, 60 mins."
This does specify the second area (cols I:J) and then specifies the first cell in that area (col I)

As usual there are other ways that we could have specified the cells to colour and/or put values in.

 

[Ironman]

Wow, that is clever ?

Could I perhaps ask you for a more 'transparent' alternative that I can understand more easily and apply myself in a future situation please?

Thanks again Peter!

 

[Peter_SSs]

Could I perhaps ask you for a more 'transparent' alternative that I can understand more easily and apply myself in a future situation please?

Sure, try this one

Sub FillEndRowBlue_v3()
  Dim NextRow As Long
  
  If ActiveSheet.Name = "Training Log" Then
    NextRow = Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("A" & NextRow).Resize(, 6).Interior.Color = RGB(197, 217, 241)
    Range("I" & NextRow).Resize(, 2).Interior.Color = RGB(197, 217, 241)
    Range("B" & NextRow).Value = "OTHER"
    Range("I" & NextRow).Value = "Indoor bike session, 60 mins."
  Else
    MsgBox "Cell fill does not work in this sheet", vbInformation, "Information"
  End If
End Sub