Is it possible to use a macro to delete only cells formated in green ? In the
same spreadsheet I also have no color data, but the green data changes
weekly. I'd like to be able to run a macro that will identify which cells are
formatted in green, and delete only the data in those green cells. Would I
use/create a yes or no type column, yes being green, no being not green, and
then if cell x3 is yes, then delete data in cells a3:m3, if x3 is no, "". ??

Thanks,

Steve

Hi Steve

yes,
e.g.
sub deletegreen()
For Each c In Range("A1:A100") 'adjust range as necessary
If c.Interior.ColorIndex = 35 Then c.Value = Null
Next
end sub

---
however, you'll need to get the "right" colorindex for the green used in
your workbook - the easiest way to do this is record a macro colouring a cell
green ... then choose tools / macro / macros ... click on your macro and edit
... and you'll see the colorindex number.

hope this helps
Cheers
JulieD

"Steve" wrote:

Is it possible to use a macro to delete only cells formated in green ? In the
same spreadsheet I also have no color data, but the green data changes
weekly. I'd like to be able to run a macro that will identify which cells are
formatted in green, and delete only the data in those green cells. Would I
use/create a yes or no type column, yes being green, no being not green, and
then if cell x3 is yes, then delete data in cells a3:m3, if x3 is no, "". ??

Thanks,

Steve

Nothing happened. I probably have something wrong.
This is the Macro:

Sub delgreens()
'
' delgreens Macro
' Macro recorded 3/16/2005 by steve
'

'
For Each c In Range("A3:L1000")
If c.Interior.ColorIndex = 10 Then c.Value = Null
Next

End Sub

Thanks,

Steve


"JulieD" wrote:

Hi Steve

yes,
e.g.
sub deletegreen()
For Each c In Range("A1:A100") 'adjust range as necessary
If c.Interior.ColorIndex = 35 Then c.Value = Null
Next
end sub

---
however, you'll need to get the "right" colorindex for the green used in
your workbook - the easiest way to do this is record a macro colouring a cell
green ... then choose tools / macro / macros ... click on your macro and edit
.. and you'll see the colorindex number.

hope this helps
Cheers
JulieD

"Steve" wrote:

Is it possible to use a macro to delete only cells formated in green ? In the
same spreadsheet I also have no color data, but the green data changes
weekly. I'd like to be able to run a macro that will identify which cells are
formatted in green, and delete only the data in those green cells. Would I
use/create a yes or no type column, yes being green, no being not green, and
then if cell x3 is yes, then delete data in cells a3:m3, if x3 is no, "". ??

Thanks,

Steve

Hi Steve

the macro looks fine, so when you select the sheet and choose tools / macro
/ macros dalgreens and press the RUN button does anything happen at all?

is your security setting (tools / macro /macros - security) set to medium
and if you are asked when opening to the workbook to enable or disable
macros are you choosing enable?

Cheers
JuileD

"Steve" wrote in message
...
Nothing happened. I probably have something wrong.
This is the Macro:

Sub delgreens()
'
' delgreens Macro
' Macro recorded 3/16/2005 by steve
'

'
For Each c In Range("A3:L1000")
If c.Interior.ColorIndex = 10 Then c.Value = Null
Next

End Sub

Thanks,

Steve


"JulieD" wrote:

Hi Steve

yes,
e.g.
sub deletegreen()
For Each c In Range("A1:A100") 'adjust range as necessary
If c.Interior.ColorIndex = 35 Then c.Value = Null
Next
end sub

---
however, you'll need to get the "right" colorindex for the green used in
your workbook - the easiest way to do this is record a macro colouring a
cell
green ... then choose tools / macro / macros ... click on your macro and
edit
.. and you'll see the colorindex number.

hope this helps
Cheers
JulieD

"Steve" wrote:

Is it possible to use a macro to delete only cells formated in green ?
In the
same spreadsheet I also have no color data, but the green data changes
weekly. I'd like to be able to run a macro that will identify which
cells are
formatted in green, and delete only the data in those green cells.
Would I
use/create a yes or no type column, yes being green, no being not
green, and
then if cell x3 is yes, then delete data in cells a3:m3, if x3 is no,
"". ??

Thanks,

Steve

Julie,

The macro does run, but the green formatted cells are still there. I even
double checked the color index, and even substituted xlAutomatic to see if
the automatic formatted cells would be cleared and they also did not clear.

Thanks,

Steve

"JulieD" wrote:

Hi Steve

the macro looks fine, so when you select the sheet and choose tools / macro
/ macros dalgreens and press the RUN button does anything happen at all?

is your security setting (tools / macro /macros - security) set to medium
and if you are asked when opening to the workbook to enable or disable
macros are you choosing enable?

Cheers
JuileD

"Steve" wrote in message
...
Nothing happened. I probably have something wrong.
This is the Macro:

Sub delgreens()
'
' delgreens Macro
' Macro recorded 3/16/2005 by steve
'

'
For Each c In Range("A3:L1000")
If c.Interior.ColorIndex = 10 Then c.Value = Null
Next

End Sub

Thanks,

Steve


"JulieD" wrote:

Hi Steve

yes,
e.g.
sub deletegreen()
For Each c In Range("A1:A100") 'adjust range as necessary
If c.Interior.ColorIndex = 35 Then c.Value = Null
Next
end sub

---
however, you'll need to get the "right" colorindex for the green used in
your workbook - the easiest way to do this is record a macro colouring a
cell
green ... then choose tools / macro / macros ... click on your macro and
edit
.. and you'll see the colorindex number.

hope this helps
Cheers
JulieD

"Steve" wrote:

Is it possible to use a macro to delete only cells formated in green ?
In the
same spreadsheet I also have no color data, but the green data changes
weekly. I'd like to be able to run a macro that will identify which
cells are
formatted in green, and delete only the data in those green cells.
Would I
use/create a yes or no type column, yes being green, no being not
green, and
then if cell x3 is yes, then delete data in cells a3:m3, if x3 is no,
"". ??

Thanks,

Steve

Hi Steve

in your original post you said:
"I'd like to be able to run a macro that will identify which cells are
formatted in green, and delete only the data in those green cells."
which is what the code i gave you does when i test it ... however, i didn't
realise that you also wanted to remove the green colour

try

Sub delgreens()
For Each c In Range("A3:L1000")
If c.Interior.ColorIndex = 10 Then
c.Value = Null
c.Interior.ColorIndex = xlNone
End if
Next
End Sub

----
Cheers
JulieD

"Steve" wrote in message
...
Julie,

The macro does run, but the green formatted cells are still there. I even
double checked the color index, and even substituted xlAutomatic to see
if
the automatic formatted cells would be cleared and they also did not
clear.

Thanks,

Steve

"JulieD" wrote:

Hi Steve

the macro looks fine, so when you select the sheet and choose tools /
macro
/ macros dalgreens and press the RUN button does anything happen at all?

is your security setting (tools / macro /macros - security) set to medium
and if you are asked when opening to the workbook to enable or disable
macros are you choosing enable?

Cheers
JuileD

"Steve" wrote in message
...
Nothing happened. I probably have something wrong.
This is the Macro:

Sub delgreens()
'
' delgreens Macro
' Macro recorded 3/16/2005 by steve
'

'
For Each c In Range("A3:L1000")
If c.Interior.ColorIndex = 10 Then c.Value = Null
Next

End Sub

Thanks,

Steve


"JulieD" wrote:

Hi Steve

yes,
e.g.
sub deletegreen()
For Each c In Range("A1:A100") 'adjust range as necessary
If c.Interior.ColorIndex = 35 Then c.Value = Null
Next
end sub

---
however, you'll need to get the "right" colorindex for the green used
in
your workbook - the easiest way to do this is record a macro colouring
a
cell
green ... then choose tools / macro / macros ... click on your macro
and
edit
.. and you'll see the colorindex number.

hope this helps
Cheers
JulieD

"Steve" wrote:

Is it possible to use a macro to delete only cells formated in green
?
In the
same spreadsheet I also have no color data, but the green data
changes
weekly. I'd like to be able to run a macro that will identify which
cells are
formatted in green, and delete only the data in those green cells.
Would I
use/create a yes or no type column, yes being green, no being not
green, and
then if cell x3 is yes, then delete data in cells a3:m3, if x3 is
no,
"". ??

Thanks,

Steve