I want to write a simple macro that I can run on any worksheet that will look
for the value "C" in column "B" and if this value exists change the color of
the corresponding cell in column "G" to soft yellow.
Each worksheet will have a variable number of rows.

I don't want to use conditional formatting and copy/paste because I need to
run it every month on a different worksheet, so I want a macro that says for
the "active worksheet" the above happens

thanks in advance
marcia

Columns("B:B").Select
Selection.Find(What:="c", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(rowoffset:=0, columnoffset:=5).Activate
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

"marcia2026" wrote:

> I want to write a simple macro that I can run on any worksheet that will look
> for the value "C" in column "B" and if this value exists change the color of
> the corresponding cell in column "G" to soft yellow.
> Each worksheet will have a variable number of rows.
>
> I don't want to use conditional formatting and copy/paste because I need to
> run it every month on a different worksheet, so I want a macro that says for
> the "active worksheet" the above happens
>
> thanks in advance
> marcia

Hi Sean, It worked great on the 1st one it found, but not any of the ones
after that.

"Sean Timmons" wrote:

> Columns("B:B").Select
> Selection.Find(What:="c", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
> :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:= _
> False, SearchFormat:=False).Activate
> ActiveCell.Offset(rowoffset:=0, columnoffset:=5).Activate
> With Selection.Interior
> .ColorIndex = 6
> .Pattern = xlSolid
> End With
>
> "marcia2026" wrote:
>
> > I want to write a simple macro that I can run on any worksheet that will look
> > for the value "C" in column "B" and if this value exists change the color of
> > the corresponding cell in column "G" to soft yellow.
> > Each worksheet will have a variable number of rows.
> >
> > I don't want to use conditional formatting and copy/paste because I need to
> > run it every month on a different worksheet, so I want a macro that says for
> > the "active worksheet" the above happens
> >
> > thanks in advance
> > marcia

Hi,

I'm using Excel 2000,

When I ran the code in a private sub in the Sheet1 module, it fails on

Selection.Find(What:="c", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

with a run time error 448, Named argument not found.

I'm not sure what I've missed.

thanks,

Dan