How do I create a macro that only runs on cells I select?

Question

My experience of macros and VBA has so far been limited to creating a button to colour in cells so you'll have to excuse this if it's a possible easy fix.

I have a template sheet into which will be pasted data on a weekly basis containing both headers and tasks - I'm looking to filter the headers only, then format these rows and unfilter, automating this using a macro.

I've recorded the following to do this: -

    ActiveSheet.Range("$A$4:$H$413").AutoFilter Field:=2, Criteria1:="="
    ActiveWindow.SmallScroll Down:=-9
    Range("A5:L168").Select
    Range("A168").Activate
    Selection.SpecialCells(xlCellTypeVisible).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 3394611
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Font.Bold = True
    Range("I5:L168").Select
    Range("L168").Activate
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.ClearContents
    Range("C5:H168").Select
    With Selection.Font
        .Color = -13382605
        .TintAndShade = 0
    End With
    ActiveSheet.Range("$A$4:$H$413").AutoFilter Field:=2
    Range("A4").Select

However this formats the same rows every time the macro is run, and so when different data is pasted into the template (containing headers in different locations) the macro-driven formatting then doesn't pick up the header rows.

Is there any way to adjust the range so that the macro will only be applied to certain cells selected by the user (for example the remaining visible rows when I filter the data)?

Step 1: [Make sure that the rest of your code doesn't use `Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) Step 2: Use a `For Each` or `With` on `Selection` — Chronocidal, Jul 04 '19 at 15:49
Step 0: make sure `TypeOf Selection Is Excel.Range` (CC @Chronocidal) — Mathieu Guindon, Jul 04 '19 at 17:09
That said, you shouldn't need to care for the `Selection` - you can work out what the last row is and then work with `.Range("A4:H" & lastRow)` to format the whole table — Mathieu Guindon, Jul 04 '19 at 17:11

score 1 · Answer 1 · answered Jul 05 '19 at 03:29

1

Select the cells you want to change. They do not need to be contiguous.

This code loops through the selected cells and changes the background color to blue:

For Each i In Selection
    i.Interior.ColorIndex = 5
Next i

answered Jul 05 '19 at 03:29

Jumpgroup

161
1
6

The code above works because "Selection" on the current worksheet returns a Range object containing the list of selected cells. In the loop, you're assigning a cell object to the variable "i" in each loop iteration. Therefore, you can assign (or return) any property or method to "i" that you would use on any cell object. – Jumpgroup Jul 13 '19 at 16:25

How do I create a macro that only runs on cells I select?

1 Answers1