Color Macro

[Altum681]

Hello.

I would like to create a macro that says, if the cell color is green, then make the cell value 0. I have figured out ways to change cell color based on value, but not how to turn the code around. Any ideas?

Thanks!

 

[Dendro]

The cell is manually turned green. I go through and adjust the cells that I want to always be 0. I update the spreadsheet monthly, and most cells that start green, stay green each month. I just overwrite the "B4" value in the example several times a day for the first week of the month as prices come in. Since there are 20-30 cells that will always be 0, regardless of what price comes in, I am trying to determine an easy way to force them to 0 without manually adjusting all of them.

At the first post you said all green cells should be changed to contain 0. What would the criteria be to decide whether a green cell gets the value 0, how do you decide when you do it manually?The 20-30 cells that will always be 0 can be used to create a continous list in another sheet on which the macro can match cells in the first sheet.

 

[Altum681]

The color is working, that has been tested. Current code is

i = 2 'start row number

For Each c In ActiveSheet.Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)

If Cells(i, 1).Interior.ColorIndex = 4 Then

Rng = "A" & i & ":" & "A" & i
Range(Rng).Value = "0"

Else

End If

i = i + 1

Next c

End Sub

This reads that if the cell is bright green (I don't really care what the original color is), then make the cell 0. It works for the first green cell, but my loop is breaking somewhere.

 

[Altum681]

That could work. I could name the cells to ensure the code doesn't break when new rows/columns are added. I liked the color method because that makes it obvious to anyone looking at it why those prices don't match. This may not be an option though. I'm close, but like the next code shows, the code is struggling to keep looping. I can make 2 green cells produce 0s, but then it stops.