Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Let's say I create a macro that solid fills over text from B2:F2. I would like this same thing to be possible on all other rows without writing a macro for hundreds of other rows.

Is there a way that I can write this macro and then have it apply to the row that I have selected rather than to only the row that I used to record the macro?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

This will solid fill "yellow" for anything you have selected.

Sub Macro1()
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Alternatively, if I read your question correctly (and you're talking about Microsoft Excel), you want to fill columns B through F of your current row. This will do that in yellow, all depending on what cell you have selected when you run it.

Sub Macro1()
    'define the range variable that will be filled
    Dim currentrange As Range
    'define the row number variable
    Dim Rownum As Integer

    'set our rownum variable to the current selection's row
    Rownum = Selection.Row

    'set our range variable to the current row's columns 2-6
    Set currentrange = Range((Cells(Rownum, 2)), (Cells(Rownum, 6)))

    'let's start filling!
    With currentrange.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        'pick your color here
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub
share|improve this answer
    
Raystafarian, this was very helpful. Thanks –  tim Mar 3 '12 at 23:24
1  
Good clean answer. +1. My only criticism is .Color = 65535. Why 65535? Converting this to hexidecimal 00FFFF does not help because Excel uses non-standard numbers for its colours. I would write .Color = RGB(255,255,0) which says I want full red + full green + no blue. I know you were taught that yellow is a primary colour at school but that is only for paint; light is different. Google for "HTML colours" and you will find sites that say what you get for every combination of red, green and blue. However, Excel only supports 40 different colours so don't try anything exotic. –  Tony Dallimore Mar 4 '12 at 10:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.