Find and replace with condition

S

Stuart

Help - I want to update values in a column, but this is with conditions in
other cells e.g.
A B C
1 blue Fred
2 black Jim
3 blue Steve

change blue to red in column A, where content of B = Fred

Can this be done?
 
B

Bob Phillips

For i = 1 To Cells(Rows.Count,"A").End(xlUp).Row
If Cells(i,"A").Value = "blue" And Cells(i,"B").Value = "Fred") Then
Cells(I,"A").Value = "red"
End If
Next i

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

You have to put it in a sub

Sub myMacro()

'the code

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
S

Stuart

Hi,

I've tried this code, but it doesn't seem to work. It will compile and run,
but nothing happens. I'd expect cell A1 to change from blue to red, but it
remains blue. Any more help?!

Thanks.
 
D

Dave Peterson

If you have Blue or BLUE or BLue or BLUe, it won't work the way you want.

In VBA, upper and lower case characters are not considered the same.

Maybe...

Sub myMacro()

For i = 1 To Cells(Rows.Count,"A").End(xlUp).Row
If lcase(Cells(i,"A").Value)= "blue" _
And lcase(Cells(i,"B").Value) = "fred") Then
Cells(I,"A").Value = "red"
End If
Next i

end sub
 
Top