Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

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
  • 4
2 Solutions
Juan OcasioCommented:
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 OcasioCommented:

This line:

For y = 5 To LastRow Step 5

should read

For y = 5 To LastRow Step 10
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

.Color = 12566463

Sorry, didn't read the whole thing
AndreasHermleAuthor 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 OcasioCommented:
I'm glad we could be of assitance!

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now