insert coloured row above filled in cells

[Darren Smith]

I`ve got ranges on spreadsheets which need a coloured row inserted above the rows which have the data filled into cells.

Trust this makes sense? if not please tell me

 

[afonsomira]

I really did not understand what you want, I will leave two codes.

Add a colored line at the top of the first cell in Column A that has value:

Sub addOneRow()

Dim firstRow As Integer
firstRow = ActiveSheet.Cells(1, 1).End(xlDown).Row

Rows(firstRow).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlAbove
With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Adds colored line at the top of all cells in column A that have values:

Sub addRows()

Dim firstRow As Integer, i As Integer
Dim lastRow As Long

firstRow = ActiveSheet.Cells(1, 1).End(xlDown).Row
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For i = lastRow To firstRow Step -1

Rows(i).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlAbove
With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
Next i
End Sub

 

[Darren Smith]

Thanks for the above the second one is almost what i need but i need to assign the code to a range variable.
Also sorry it is just to color to the row rather the inserting a row.

 

[afonsomira]

maybe this:

Sub addRows()

Dim firstRow As Integer, i As Integer
Dim lastRow As Long

firstRow = ActiveSheet.Cells(1, 1).End(xlDown).Row
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For i = lastRow To firstRow Step -1

Rows(i).Select
With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
Next i
End Sub

 

[Darren Smith]

Sorry it needs to take a range from a variable
Can you call a variable in for the range.

The Range variable is below

Sub Page_Index_Dest()

Dim Lb As ListBox
Dim ws As Worksheet
Set Lb = Body_And_Vehicle_Type_Form.ListBox3
Set ws = ThisWorkbook.Sheets("Job Card with Time Analysis")

If Lb.Value = ("1 Page Job Card Master.xlsm") Then
ws.Activate
Range("A13:N67").Select
End If

If Lb.Value = ("2 Page Jobcard Master.xlsm") Then
ws.Activate
Range("A13:N61", "A66:N120").Select
End If

If Lb.Value = ("3 Page Jobcard Master.xlsm") Then
ws.Activate
Range("A13:N61", "A66:N122", "A127:183").Select
End If

If Lb.Value = ("4 Page Jobcard Master.xlsm") Then
ws.Activate
Range("A13:N61", "A66:N122", "A127:183", "A188:N244").Select
End If

If Lb.Value = ("5 Page Jobcard Master.xlsm") Then
ws.Activate
Range("A13:N61", "A66:N122", "A127:183", "A188:N244", "A249:299").Select
End If

End Sub

 

[Darren Smith]

Afternoon All

Please can somebody HELP with above

Mt

 

[Darren Smith]

Morning All

Please help me with the above question

 

[EFANYoutube]

Hi Darren,
So, if I said starting at row 1 of the sheet, if this row is completely empty then colour it. Then row 2 and so on. Everytime it finds a completely empty row it colors it?
Am i getting close to what the solution would look like?

 

[Darren Smith]

Hi yes, you are.
But it`s linked to the ranges shown above.
Also, there are 2 empty rows between filled in rows it`s the lower of the 2 rows that need color.

 

[Darren Smith]

Can you select the lower row of the 2 empty rows using VBA?