Macro

[Paul H]

I have created a simple macro that when used will clear out the contents of a
certain cells. If I insert an additional row the macro does not adjust and
the macro needs to be edited. Is there a way to have the macro adjust
automatically when a row is insurted?

[Jim Thomlinson]

Nope. The Macro is what it is... You need to change the macro to dynamically
determine what it needs to do, not clear the contenst of a "Hard Coded" cell.
I would need to see the code and get a few more details to help you...

"Paul H" wrote:

> I have created a simple macro that when used will clear out the contents of a
> certain cells. If I insert an additional row the macro does not adjust and
> the macro needs to be edited. Is there a way to have the macro adjust
> automatically when a row is insurted?

[Paul H]

Can a macro be written that would clear the contents from all yellow cells?

"Jim Thomlinson" wrote:

> Nope. The Macro is what it is... You need to change the macro to dynamically
> determine what it needs to do, not clear the contenst of a "Hard Coded" cell.
> I would need to see the code and get a few more details to help you...
>
> "Paul H" wrote:
>
> > I have created a simple macro that when used will clear out the contents of a
> > certain cells. If I insert an additional row the macro does not adjust and
> > the macro needs to be edited. Is there a way to have the macro adjust
> > automatically when a row is insurted?

[Gary Brown]

Paul,
If you create a range containing those certain cells then inserting or
deleting a row or column will adjust the range. In a macro, reference the
range name instead of the individual cells.
For example:

If you created a range name called TEMP in A1:A2 (Insert / Name / Define),
then insert a row at at row 2, the range TEMP will know that the new range is
A1:A3. An example of using a range instead of cell addresses would be...
Range("TEMP").Copy
Range("D1").Select
ActiveSheet.Paste

Hope this helps,
Gary Brown


"Paul H" wrote:

> I have created a simple macro that when used will clear out the contents of a
> certain cells. If I insert an additional row the macro does not adjust and
> the macro needs to be edited. Is there a way to have the macro adjust
> automatically when a row is insurted?

[Gary Brown]

Yes,
Sub test()
Dim cell As Range
For Each cell In Selection
If cell.Interior.ColorIndex = 6 Then
cell.ClearContents
End If
Next cell
End Sub

6 is bright yellow
36 is light yellow

or using Range Names...
Sub test()
Dim cell As Range
For Each cell In Range("TEST")
If cell.Interior.ColorIndex = 6 Then
cell.ClearContents
End If
Next cell
End Sub

HTH,
Gary Brown


"Paul H" wrote:

> Can a macro be written that would clear the contents from all yellow cells?
>
> "Jim Thomlinson" wrote:
>
> > Nope. The Macro is what it is... You need to change the macro to dynamically
> > determine what it needs to do, not clear the contenst of a "Hard Coded" cell.
> > I would need to see the code and get a few more details to help you...
> >
> > "Paul H" wrote:
> >
> > > I have created a simple macro that when used will clear out the contents of a
> > > certain cells. If I insert an additional row the macro does not adjust and
> > > the macro needs to be edited. Is there a way to have the macro adjust
> > > automatically when a row is insurted?