Change colour of Excel Row

[DMT Dave]

Hi Guys, the below code has worked buy adding a blank row after each SortNo, don't know how but it worked, i now want to fill the blank rows with indexColor 37 (blue) the following code debugs after testing the 1st blank row ???

Dim m1 As Long, m2 As Long, m3 As Long, m4 As Long, m5 As Long, m6 As Long, m7 As Long, m8 As Long, m9 As Long, m10 As Long
 m1 = DCount("ItemType", "tblStock", "[SortNo] = 1")
m2 = DCount("ItemType", "tblStock", "[SortNo] = 2")
m3 = DCount("ItemType", "tblStock", "[SortNo] = 3")
m4 = DCount("ItemType", "tblStock", "[SortNo] = 4")
m5 = DCount("ItemType", "tblStock", "[SortNo] = 5")
m6 = DCount("ItemType", "tblStock", "[SortNo] = 6")
m7 = DCount("ItemType", "tblStock", "[SortNo] = 7")
m8 = DCount("ItemType", "tblStock", "[SortNo] = 8")
m9 = DCount("ItemType", "tblStock", "[SortNo] = 9")
m10 = DCount("ItemType", "tblStock", "[SortNo] = 10")


With xlWB
.Worksheets(1).Cells(3, 3).CopyFromRecordset rs
.Worksheets(1).Cells(3 + m1, 3).EntireRow.Insert 'Std
'.Worksheets(1).Range("C + m1, 3").Interior.ColorIndex = 37 (Debugs)
Also tried '.Worksheets(1).Range(3 + m1, 3).Interior.ColorIndex = 37 (Debugs)
 .Worksheets(1).Cells(3 + 1 + m1 + m2, 3).EntireRow.Insert 'Std LH
.Worksheets(1).Cells(3 + 2 + m1 + m2 + m3, 3).EntireRow.Insert 'Std RH
.Worksheets(1).Cells(3 + 3 + m1 + m2 + m3 + m4, 3).EntireRow.Insert 'Ex MS
.Worksheets(1).Cells(3 + 4 + m1 + m2 + m3 + m4 + m5, 3).EntireRow.Insert 'Ex MS LH
.Worksheets(1).Cells(3 + 5 + m1 + m2 + m3 + m4 + m5 + m6, 3).EntireRow.Insert 'Ex MS RH
.Worksheets(1).Cells(3 + 6 + m1 + m2 + m3 + m4 + m5 + m6 + m7, 3).EntireRow.Insert 'Ex PS
.Worksheets(1).Cells(3 + 7 + m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8, 3).EntireRow.Insert 'Ex LH
.Worksheets(1).Cells(3 + 8 + m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9, 3).EntireRow.Insert 'Ex RH
.Worksheets(1).Cells(3 + 9 + m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9 + m10, 3).EntireRow.Insert 'OD
.Worksheets(1).Cells.EntireColumn.AutoFit
.Worksheets(1).Cells.EntireColumn.HorizontalAlignment = xlLeft
.Worksheets(2).Cells(3, 3).CopyFromRecordset rs2
.Worksheets(2).Cells.EntireColumn.AutoFit
.Worksheets(2).Cells.EntireColumn.HorizontalAlignment = xlLeft

[June7]

What range are you trying to address?

Range() uses ":" as in Range("A1:F10")

Example from my code:

    'Sort records on Results sheet so they will be in Date then Point then LabNum order for the GEOReport
    Worksheets("Results").Range("3:" & intResults + 2).Sort _
        Key1:=Worksheets("Results").Range("A3"), Order2:=xlAscending, _
        Key2:=Worksheets("Results").Range("B3"), Order3:=xlAscending, _
        Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=xlSortNormal
    'Adjust row height of GEOReport to allow wrapped data to display
    Worksheets("GEOReport").Rows("2:1372").AutoFit
    If Worksheets("Results").Range("A3") = "" Then
        MsgBox "No data found.", vbApplicationModal, "No Samples"
        Exit Sub
    End If

In your case try:

Range(3 + m1 & ":3")
or
Range(3 + m1)