Find and replace with condition

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?

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)


"Stuart" <Stuart@discussions.microsoft.com> wrote in message
news:EA5D5071-A2B2-4CF2-A1DA-641F44288267@microsoft.com...
> 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?

I get an error message when I compile:

Invalid outside procedure


"Bob Phillips" wrote:

>
> 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)
>
>
> "Stuart" <Stuart@discussions.microsoft.com> wrote in message
> news:EA5D5071-A2B2-4CF2-A1DA-641F44288267@microsoft.com...
> > 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?
>
>
>

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)


"Stuart" <Stuart@discussions.microsoft.com> wrote in message
news:9BA1FBAB-EFF4-484F-9F2E-A596CA3511D1@microsoft.com...
> I get an error message when I compile:
>
> Invalid outside procedure
>
>
> "Bob Phillips" wrote:
>
> >
> > 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)
> >
> >
> > "Stuart" <Stuart@discussions.microsoft.com> wrote in message
> > news:EA5D5071-A2B2-4CF2-A1DA-641F44288267@microsoft.com...
> > > 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?
> >
> >
> >

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.

"Bob Phillips" wrote:

> 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)
>
>
> "Stuart" <Stuart@discussions.microsoft.com> wrote in message
> news:9BA1FBAB-EFF4-484F-9F2E-A596CA3511D1@microsoft.com...
> > I get an error message when I compile:
> >
> > Invalid outside procedure
> >
> >
> > "Bob Phillips" wrote:
> >
> > >
> > > 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)
> > >
> > >
> > > "Stuart" <Stuart@discussions.microsoft.com> wrote in message
> > > news:EA5D5071-A2B2-4CF2-A1DA-641F44288267@microsoft.com...
> > > > 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?
> > >
> > >
> > >
>
>
>

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



Stuart wrote:
>
> 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.
>
> "Bob Phillips" wrote:
>
> > 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)
> >
> >
> > "Stuart" <Stuart@discussions.microsoft.com> wrote in message
> > news:9BA1FBAB-EFF4-484F-9F2E-A596CA3511D1@microsoft.com...
> > > I get an error message when I compile:
> > >
> > > Invalid outside procedure
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > >
> > > > 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)
> > > >
> > > >
> > > > "Stuart" <Stuart@discussions.microsoft.com> wrote in message
> > > > news:EA5D5071-A2B2-4CF2-A1DA-641F44288267@microsoft.com...
> > > > > 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?
> > > >
> > > >
> > > >
> >
> >
> >

--

Dave Peterson