PDA

View Full Version : Solved: enter value in cell based on another cell



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

fb7894
10-10-2008, 11:38 AM
change this line of code

For Each x In Selection
to
For Each x In Range("A1").EntireColumn

To get the value into column B, try
x.offset(,1).FormulaR1C1 = "Red"

dragon576
10-10-2008, 01:26 PM
Thanks. The second part worked, but I can not seem to get it to select the column.

I have uploaded an example to demonstrate.

Thanks again
Doug

dragon576
10-10-2008, 01:44 PM
Figured it out. Just needed pointing in the right direction.

Used the following to get it to work.

Columns("A:A").Select

For Each x In Selection

Cheers

Doug

david000
10-10-2008, 09:08 PM
Doug, try out the UsedRange property it should speed things up a bit.

With ActiveSheet.UsedRange
For Each x In Range("a1").Resize(.Rows.Count)

If x.Interior.ColorIndex = RedNum Then
x.Offset(, 1) = "Red"
ElseIf x.Interior.ColorIndex = OrangeNum Then
x.Offset(, 1) = "Amber"
ElseIf x.Interior.ColorIndex = GreenNum Then
x.Offset(, 1) = "Green"
End If

Next x
End With

dragon576
10-11-2008, 05:25 AM
Thanks, that seems faster.