macro to put value in cell based on colour

K

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?
 
J

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
 
K

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
 
K

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
 
J

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
 
R

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
 
Top