macro to put value in cell based on colour

[Kirsty]

I want to write a macro (or formula) that allows me to place a number in a
cell if it has a certain colour, ie 4. Any suggestions?

 

[Jacob Skaria]

Sub Macro1()
'default yellow 65535
Set rngTemp = Range("A1:A10")
For Each cell In rngTemp
If cell.Interior.Color = 65535 Then cell.Value = "4"
Next
End Sub

If this post helps click Yes

 

[Kirsty]

I used this and it is still not working. Any suggestions?

Set rng = Range("R5:Y8")
For Each cell In rng
If cell.Interior.Color = 4 Then cell.Value = "100"
Next

 

[Kirsty]

I used this and it is not working. Any suggestions?

Set rng = Range("R5:Y8")
For Each cell In rng
If cell.Interior.Color = 4 Then cell.Value = "100"
Next

 

[Jacob Skaria]

I think the color you are referring to is wrong
Try the yellow or red from the standard color pallete.
Yellow is 65535 or vbYellow.

If cell.Interior.Color = vbYellow Then cell.Value = "100"
OR
If cell.Interior.Color = 65535 Then cell.Value = "100"


If this post helps click Yes

 

[Jacob Skaria]

Refer this for more help

http://www.mvps.org/dmcritchie/excel/colors.htm


If this post helps click Yes

 

[Rick Rothstein]

The number 4 looks like a ColorIndex rather than a Color; try this...

If cell.Interior.ColorIndex = 4 Then cell.Value = "100"

While assigning "100" will work (Excel seems to do a behind the scenes
conversion and makes it a number), there is no need to put the quotes around
the 100, this will work as well...

If cell.Interior.ColorIndex = 4 Then cell.Value = 100