conditional formatting based on cell contents

[marcia2026]

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

 

[Sean Timmons]

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]

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

 

[dan dungan]

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