dragon576
10-10-2008, 11:31 AM
Hi, this is likely very easy but brains turned to mush.
Two columns in sheet , A and B. Number of rows changes each week.
I want to check the colour of each cell in column A and add the word for that colour in column B.
Eg If A1 is green the B1 should have the word "Green" as it's value.
I have tried the following code which works but only on a selected cell in column A (A1 has "Green" as a Value) but no other cells in column A change and I need the value in column B.
Sub Colors()
RedNum = 3
OrangeNum = 6
GreenNum = 4
For Each x In Selection
If x.Interior.ColorIndex = RedNum Then ActiveCell.FormulaR1C1 = "Red"
If x.Interior.ColorIndex = OrangeNum Then ActiveCell.FormulaR1C1 = "Amber"
If x.Interior.ColorIndex = GreenNum Then ActiveCell.FormulaR1C1 = "Green"
Next x
End Sub
So two questions.
1. How do I get it to check every cell in column A regardless of the number of rows?
2. How can I get the right value entered in column B if a cell has one of the colours?
Many Thanks
Doug
Two columns in sheet , A and B. Number of rows changes each week.
I want to check the colour of each cell in column A and add the word for that colour in column B.
Eg If A1 is green the B1 should have the word "Green" as it's value.
I have tried the following code which works but only on a selected cell in column A (A1 has "Green" as a Value) but no other cells in column A change and I need the value in column B.
Sub Colors()
RedNum = 3
OrangeNum = 6
GreenNum = 4
For Each x In Selection
If x.Interior.ColorIndex = RedNum Then ActiveCell.FormulaR1C1 = "Red"
If x.Interior.ColorIndex = OrangeNum Then ActiveCell.FormulaR1C1 = "Amber"
If x.Interior.ColorIndex = GreenNum Then ActiveCell.FormulaR1C1 = "Green"
Next x
End Sub
So two questions.
1. How do I get it to check every cell in column A regardless of the number of rows?
2. How can I get the right value entered in column B if a cell has one of the colours?
Many Thanks
Doug