Changing value in Column A depending on value in Column A, B

[jmh1]

Hi, I have a table that has 2 columns, A and B.


Each column contains multiple values.

I need to write a macro that will look at Column A, and IF it is "1," THEN look at Column B for any cells with "Red," "blue," or "orange" and change Column A to "Yes."


If Column A is not "1," then I do not want to change Column A, regardless of what is in Col. B.

If Col. A is "1", but Col. B is not "red" "blue", or "orange", then I do not want to change Col. A.

Any help would be much appreciated.

thanks

 

[HalfAce]

I need to write a macro that will look at Column A, and IF it is "1," THEN look at Column B for any cells with "Red," "blue," or "orange" and change Column A to "Yes."

Hello jmh1,
Do you mean to look at each cell in column A and if the cell in column B in that row contains 'red', 'blue' or 'orange'?

 

[jmh1]

Yes, in that row.

Thanks!

 

[jmh1]

so if Col A-B is 1-red..would change to Yes-Red
1-blue would change to yes-blue,
1-orange change to yes-orange


2-red would remain 2-red

 

[lenze]

Untested

Sub SeeIt()
Dim cl as Range
For each cl in Range("$A:$A")
If cl = 1 Then
  Select Case cl.Offset(0,1)
  Case "Red","Blue","Orange"
  cl ="yes"
  Case Else
  End Select
End If
Next cl
End Sub

Can't test right now

lenze

 

[HalfAce]

If all that's in column B are the colors (and not anything else) then something like this should do.

Option Compare Text

Sub FindColorsDemo()
Dim Rng As Range, LstRw As Long
LstRw = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range(Cells(1, "A"), Cells(LstRw, "A"))
For Each c In Rng
  If c.Value = 1 Then _
    If c.Offset(, 1) = "red" Or _
       c.Offset(, 1) = "blue" Or _
       c.Offset(, 1) = "orange" Then c.Value = "Yes"
Next c
End Sub

 

[jmh1]

Thank you guys both very much.

HalfAce, I think your macro works better for me.

The problem is, I'm moving the Columsn over to Col C and D.. I tried to just change the "A"s in your macro to "C"s but the macro returns errors when I do so.

Do you have any advice on how i can move it to C and D?

Thanks!!

 

[jmh1]

Got it all figured out..thank you very much guys

 

[HalfAce]

Actually they should both work equally as well for you.
The only difference would be speed.

The code I posted edited to columns C & D would be:

Sub FindColorsDemo()
Dim Rng As Range, LstRw As Long, c As Range
LstRw = Cells(Rows.Count, "C").End(xlUp).Row
Set Rng = Range(Cells(1, "C"), Cells(LstRw, "C"))
For Each c In Rng
  If c.Value = 1 Then _
    If c.Offset(, 1) = "red" Or _
       c.Offset(, 1) = "blue" Or _
       c.Offset(, 1) = "orange" Then c.Value = "Yes"
Next c
End Sub

Lenze's code would be:

Sub SeeIt()
Dim cl As Range
For Each cl In Range("$C:$C")
If cl = 1 Then
  Select Case cl.Offset(0, 1)
  Case "Red", "Blue", "Orange"
  cl = "yes"
  Case Else
  End Select
End If
Next cl
End Sub

As for the difference in speed, (out of curiosity) I ran this several times on a range of
just over 11,500 rows. The code I posted was consistantly taking 0.546875 seconds
and lenze's was taking 0.640625 seconds. (A difference of 3/32 of a second on a routine
that's only taking half a second to begin with. (You'd have to be in a pretty big hurry
for that to make much difference.)

 

[lenze]

Hi Dan

Idle question. I was in a hurry and wrote the code on the fly. I wonder if speed would improve if I also limited the column length to used cells. Something like

Sub SeeIt() 
Dim cl As Range 
For Each cl In Sub SeeIt() 
Dim cl As Range 
For Each cl In ActiveSheet.Range("$C2:C" & Range("$C65536").End(xlUp).Row)  
If cl = 1 Then 
  Select Case cl.Offset(0, 1) 
  Case "Red", "Blue", "Orange" 
  cl = "yes" 
  Case Else 
  End Select 
End If 
Next cl 
End Sub

Now it wouldn't check each cell in the column. Just curious.

lenze