Code to Color ActiveRow Columns A Through C

  • Thread starter Thread starter DoctorV
  • Start date Start date
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
 
One way:

With Cells(ActiveCell.Row, 1).Resize(1, 3).Interior
.ColorIndex = 38
.Pattern = xlSolid
End With
 
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
 
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>
 
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.
 
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
 
Back
Top