Code to Color ActiveRow Columns A Through C

D

DoctorV

We have a macro with the following code:

Range("A4:C4").Select
With Selection.Interior
.ColorIndex = 38
.Pattern = xlSolid
End With

What the user really wants is whatever cell their cursor is in, for th
macro to automatically change the BackColor of Columns A B and C in th
Active row the cursor is in to ColorIndex = 38

The code attempted above only affects a specific range, whereas th
code we need, needs to be able to work ANYWHERE on the worksheet


How do we do this
 
J

JE McGimpsey

One way:

With Cells(ActiveCell.Row, 1).Resize(1, 3).Interior
.ColorIndex = 38
.Pattern = xlSolid
End With
 
F

Frank Kabel

Hi
use
sub foo()
dim rng as range
set rng = Range(cells(selection.row,1),cells(selection.row,3))
with rng.interior
ColorIndex = 38
Pattern = xlSolid
End With
end sub
 
R

Ron de Bruin

I like this one without resize
It always use the range in the activecellrow

With Cells(ActiveCell.Row, 1).Range("A1:C1").Interior
.ColorIndex = 38
.Pattern = xlSolid
End With

You don't have to count the columns then<g>
 
J

JE McGimpsey

But you do introduce two things I don't like:

1) Using A1 references as indexes In this case it doesn't really matter
as much because you're starting in column 1, but I tend get confused
when I seethings like Cells(ActiveCell.Row, 23).Range("A1:C4"). I
prefer to think of A1 as always being at the top left corner of the
worksheet, period.

2) Having to resolve the range reference. Resize is very fast compared
to resolving a string references (though I'm not sure whether the
compiler resolves it on the first pass or not).

Mostly just personal preference.
 
R

Ron de Bruin

Hi J.E

For this example you can use it
Most people have problems with offset/resize ...

That's why I posted it as a alternative
 
Top