Insert Rows & Fill a Range of Cells

[Emeng]

Hi all

I wish to colour a range of cells in a newly inserted line. I have no trouble inserting the line but my code for identifying the cells is not compatible. The code trips on the ".Interior.Color" line.

.Rows(lRow & myRange).Interior.Color = 9868950

Sub InsertRows_SeparateCranes() 'separate by Earl.start date, then by Oper.WorkCenter
Const s = "Cranes"

    Dim FX5 As String
    Dim lRow As Long
    Dim lastrow As Long
    Dim myRange As Range
      
    Set myRange = Range("B:O")
  
    FX5 = "=IF(LEN(B2)>0,F2,F1)"  'inserts dates into helper column to allow for separation of days
  
    lastrow = Sheets(s).Range("B" & Rows.Count).End(xlUp).Row
  
    With Sheets(s)
        .Range("R2:R" & lastrow).Formula = FX5 'R = helper column
    End With
  
    With Sheets(s)
  
        For lRow = .Cells(.Rows.Count, "A").End(xlUp).Row To 3 Step -1
            If .Cells(lRow, "R") <> .Cells(lRow - 1, "R") Then  'R = helper column
                .Rows(lRow).EntireRow.Insert    'insert rows between date changes for each RWC
                .Rows(lRow).EntireRow.RowHeight = 6
                .Rows(lRow & myRange).Interior.Color = 9868950

            End If
        Next lRow
    End With
      
End Sub

Any help is much appreciated.

Regards Mark

Thread moved to VBA Macros

 

[Deepak]

Is this what u are looking for!!

 .Range(.Cells(lRow, "B"), .Cells(lRow, "O")).Interior.Color = 9868950

 

[Emeng]

Sure is!

Thanks Deepak

Regards Mark

 

[Emeng]

Hi Deepak

I have also tried to use your solution for another situation but it flags a '1004 run time error'.

Sub SubtotalCranes2()  ' Subtotal by daily hours for each OpWkCtr

Range("A1").Select
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(8), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
       
End Sub

Sub Format_SubtotalRows()
    'Dim ws As Worksheet
    Dim myRange As Range
   
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
   
    With Sheets("Cranes")
        .Activate
        .Rows("1:1").Insert
        .Rows("1:1").Insert
    Set myRange = Range("A1:A1000")
        myRange.AutoFilter 1, "*Grand*"
        myRange.Parent.AutoFilter.Range.EntireRow.Delete
        myRange.Parent.AutoFilterMode = False
       
        myRange.AutoFilter 1, "*Total"
        myRange.Parent.AutoFilter.Range(.Cells(lRow, "B"), .Cells(lRow, "O")).Interior.Color = 14540253
        myRange.Parent.AutoFilterMode = False
       
        .Rows("1:1").Delete
                     
    End With

End Sub

Does it not work with autofilter, or does the range need to be declared?

Regards Mark