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

jmh1

New Member
Joined
Aug 4, 2005
Messages
43
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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

New Member
Joined
Aug 4, 2005
Messages
43
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

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

Untested
Code:
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
If all that's in column B are the colors (and not anything else) then something like this should do.
Code:
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

New Member
Joined
Aug 4, 2005
Messages
43

ADVERTISEMENT

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!!
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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:
Code:
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:
Code:
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.) :LOL:
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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

Code:
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
 

Forum statistics

Threads
1,136,878
Messages
5,678,312
Members
419,754
Latest member
LordEddard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top