insert coloured row above filled in cells

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
378
Office Version
  1. 2019
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

afonsomira

Board Regular
Joined
Aug 11, 2020
Messages
62
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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:

VBA Code:
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

Active Member
Joined
Nov 23, 2020
Messages
378
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Aug 11, 2020
Messages
62
Office Version
  1. 365
Platform
  1. Windows
maybe this:

VBA Code:
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

Active Member
Joined
Nov 23, 2020
Messages
378
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

The Range variable is below

VBA Code:
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
 
Last edited:

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
378
Office Version
  1. 2019
Platform
  1. Windows
Afternoon All

Please can somebody HELP with above

Mt
 

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
378
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Morning All

Please help me with the above question
 

EFANYoutube

Board Regular
Joined
May 19, 2017
Messages
162
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

Active Member
Joined
Nov 23, 2020
Messages
378
Office Version
  1. 2019
Platform
  1. Windows
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

Active Member
Joined
Nov 23, 2020
Messages
378
Office Version
  1. 2019
Platform
  1. Windows
Can you select the lower row of the 2 empty rows using VBA?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,865
Messages
5,627,348
Members
416,243
Latest member
ReetuC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top