Apply interior colour in a With-function to a certain range

In addition to

  • declaring all your variables, (put Option Explicit at the top of your module; also, under Tools/Options/Editor select to require variable declaration which will do that for any new modules),

  • you need to start at the first row of data.

  • You may want to clear the formats before you start.

  • Since you are processing based on data in column 9, you should be setting your range based on that column and not column 1

eg:

Option Explicit
Sub Apply_Interior_Colour()
  Dim lr As Long, fr As Long, i As Long
  Dim grey As Long, yellow As Long, interior_colour As Long

With Sheet1

'You may or may not want/need this line
'Best to restrict to just the area you are coloring
.Cells.ClearFormats

lr = .Cells(.Rows.Count, 9).End(xlUp).Row

'You can **find** the first row, or just set it, whatever is appropriate
fr = 14

grey = RGB(217, 217, 217)
yellow = RGB(255, 255, 0)
    
    For i = fr To lr
    If i = fr Then
    interior_colour = RGB(217, 217, 217)
    ElseIf .Cells(i, 9).Value <> .Cells(i, 9).Offset(-1, 0).Value Then

        If interior_colour = grey Then
        interior_colour = yellow
        Else
        interior_colour = grey
        End If
        
    End If
        
    .Range(.Cells(i, 9), .Cells(i, 12)).Interior.Color = interior_colour
        
    Next i
        
End With

End Sub

enter image description here

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top