Fill every tenth cell in Column A with a specific shade (RGB 191, 191, 191)

Dear Experts:

I would like to apply a specific shade to every tenth cell in column A starting from A5, i.e.

A5; A15; A25; A35; A45 etc.

An input box should prompt the user how many times the shading is to be applied.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Andreas HermleTeam leaderAsked:
Who is Participating?
Here is one approach:
Sub x()

Dim n As Long, i As Long

n = Application.InputBox("How many times?", Type:=1)

For i = 0 To n - 1
    Range("A5").Offset(10 * i).Interior.Color = RGB(191, 191, 191)
Next i

End Sub

Open in new window

Juan OcasioApplication DeveloperCommented:
Try this:
Sub colorMe()
Dim x As Integer
Dim y As Integer
Dim i As Integer
Dim LastRow As Integer
LastRow = ActiveSheet.UsedRange.Rows.Count
x = InputBox("How many to shade?", "Shade Me")
i = 0
For y = 5 To LastRow Step 5

Cells(y, 1).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
i = i + 1
If i = x Then Exit For

Next y

End Sub

Open in new window

Juan OcasioApplication DeveloperCommented:

This line:

For y = 5 To LastRow Step 5

should read

For y = 5 To LastRow Step 10
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Juan OcasioApplication DeveloperCommented:
One other thing:  for your color, it should be

.Color = 12566463

Sorry, didn't read the whole thing
Andreas HermleTeam leaderAuthor Commented:
Dear Both,

great job from both of you. Both codes work just fine. I awarded Stephen a couple of more points since his code does not use 'selection' and is more concise.

Again, thank you very much for your great help.

Regards, Andreas
Juan OcasioApplication DeveloperCommented:
I'm glad we could be of assitance!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.