Macro to delete data in 'green' cells only

[Steve]

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

 

[JulieD]

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]

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]

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]

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]

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

 

[Steve]

Julie,

I apologize for not being clear initially, and may even further muddy the
picture.

Here's what I have, and unfortunately, it's running, but not working:

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

'
For Each c In Range("a3:l1000")
If c.Interior.ColorIndex = 50 Then
c.Value = Null
c.Interior.ColorIndex = xlNone
End If
Next
Range("J1").Select

End Sub

I originally used green because I thought it would be easier to understand
what I was trying to do. What I really need is to clear the contents
"selection.ClearContents" of those non- green cells.

I tried altering your macro for the clear contents of the xlautomatic (not
green cells), but still haven't had any luck.

Here's my modified macro.

Sub delnotgreens()
'
' delnotgreens Macro
' Macro recorded 3/16/2005 by Steve
'

'
For Each c In Range("a3:l1000")
If c.Interior.ColorIndex = xlAutomatic Then
Selection.ClearContents
End If
Next
Range("J1").Select

End Sub

I really appreciate all you help, and wouldn't blame you if you quit here,
because I wasted a lot of your time by not being very clear in my posts,
which again, I apologize for.

Thanks again,

Steve

 

[JulieD]

Hi Steve

i don't quite that easily

if you'ld like to email me direct a sample of your file, i'll have a look at
it and see if i can figure out what is amiss ... julied_ng at hcts dot net
dot au

Cheers
JulieD