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

Posted on 2011-05-05
Last Modified: 2012-06-21
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
Question by:AndreasHermle
    LVL 14

    Assisted Solution

    by:Juan Ocasio
    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

    LVL 14

    Expert Comment

    by:Juan Ocasio

    This line:

    For y = 5 To LastRow Step 5

    should read

    For y = 5 To LastRow Step 10
    LVL 24

    Accepted Solution

    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

    LVL 14

    Expert Comment

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

    .Color = 12566463

    Sorry, didn't read the whole thing

    Author Closing Comment

    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
    LVL 14

    Expert Comment

    by:Juan Ocasio
    I'm glad we could be of assitance!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Suggested Solutions

    Title # Comments Views Activity
    Excel -- need lookup or match function 4 33
    Vlookup for IP 3 33
    Copy Value of cell in formula 1 24
    VBA filters 2 21
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now