copy and paste to another sheet based in cell color

I am trying to write code which will automate a long process. If cells
in columns D or E are red(3) or gold(44), I want it to copy the info in
coulmn B (in the corresponding row)to another sheet called 'MP.' Is
there a way to do this?
Also, I thought I might note that the cells in columns D and E are red
or gold due to conditional formatting.
Thanks.

It might be easier to just loop through each row and use the same conditions
to determine what to do.

(pseudo code)

For rw = 1 to lastrow
if cells(rw,4) = condition1 then
' your code
elseif cells(rw,5)=condition2 then
' your code
else
' anyother code you want
end if
next


for copying -
Sheets("MP").Range(????)=Sheets("MySheet").Range(rw,2)
or
Sheets("MP").Range(????).value=Sheets("MySheet").Range(rw,2).value
or
Sheets("MP").Range(????).text=Sheets("MySheet").Range(rw,2).text


the trick is to define lastrow, and to define the row to paste to...

write back if you need more...

--
steveB

Remove "AYN" from email to respond
"nat3ten" <nat3ten@hotmail.com> wrote in message
news:1120684285.311919.47420@o13g2000cwo.googlegroups.com...
>I am trying to write code which will automate a long process. If cells
> in columns D or E are red(3) or gold(44), I want it to copy the info in
> coulmn B (in the corresponding row)to another sheet called 'MP.' Is
> there a way to do this?
> Also, I thought I might note that the cells in columns D and E are red
> or gold due to conditional formatting.
> Thanks.
>

Sorry... need more help. I'm new to the VBA world. Can you elaborate a
little more?

Steve,

I can't answer but I want to clarify the user's intent: I think they are
talking about usging a cell format change to fire off some code ... and I
don't know of any event which will register that change.

Bill
"STEVE BELL" <AYNrand451@verizon.net> wrote in message
news:PXXye.27809$mr4.19841@trnddc05...
> It might be easier to just loop through each row and use the same
> conditions to determine what to do.
>
> (pseudo code)
>
> For rw = 1 to lastrow
> if cells(rw,4) = condition1 then
> ' your code
> elseif cells(rw,5)=condition2 then
> ' your code
> else
> ' anyother code you want
> end if
> next
>
>
> for copying -
> Sheets("MP").Range(????)=Sheets("MySheet").Range(rw,2)
> or
> Sheets("MP").Range(????).value=Sheets("MySheet").Range(rw,2).value
> or
> Sheets("MP").Range(????).text=Sheets("MySheet").Range(rw,2).text
>
>
> the trick is to define lastrow, and to define the row to paste to...
>
> write back if you need more...
>
> --
> steveB
>
> Remove "AYN" from email to respond
> "nat3ten" <nat3ten@hotmail.com> wrote in message
> news:1120684285.311919.47420@o13g2000cwo.googlegroups.com...
>>I am trying to write code which will automate a long process. If cells
>> in columns D or E are red(3) or gold(44), I want it to copy the info in
>> coulmn B (in the corresponding row)to another sheet called 'MP.' Is
>> there a way to do this?
>> Also, I thought I might note that the cells in columns D and E are red
>> or gold due to conditional formatting.
>> Thanks.
>>
>
>

The format change does not need to fire off the code. I was planning to
maunally run the marco once all formatting is in place. Thank you.

First - please leave any previous messages in your replys. Makes it easier
to follow what has gone on before (my viewer is set to not display read
messages).

So let's walk through some code... note that any line with a ' represents a
note to the code.
Write back and let me know how it works. Also if you need more help...

"MySheet" is the name I will use to represent your data sheet (replace with
the actual name)

In your Conditional Format you indicate that you have 2 conditions.
For simplicity I'll call condtion1 as something like formula is = D1=
25
and condition 2 as something like formula is = E1 = 56

Using R1C1 notation
column D becomes = RC4= 25
column E becomes = RC5= 56

Note that Range("$D$5") = Cells(5,4)

==============================================================
Sub TransfertoMP()
Dim rw as long, lrw as long, prw as long ' define variables for row
numbers

' this finds the last used row in column A
lrw = Sheets("MySheet").Cells(Rows.COUNT, "A").End(xlUp).Row

' loop from 1st row to last row
For rw = 1 to lrw
' If cell in Column D = condition1
if cells(rw,4) = 25 then


' copy cell in MP column B to MP
Sheets("MP").Cells(prw,2) = Sheets("MySheet").Cells(rw,2)

' if cell in column E = condition2
elseif cells(rw,5)=56 then

' find first empty row on "MP"
prw = Sheets("MP").Cells(Rows.COUNT, "A").End(xlUp).Row + 1

' copy cell in MP column B to MP
Sheets("MP").Cells(prw,2) = Sheets("MySheet").Cells(rw,2)

' this one is only if you want a third option of something to do
else
' any other code you want
end if
next

End Sub
======================================

--
steveB

Remove "AYN" from email to respond
"STEVE BELL" <AYNrand451@verizon.net> wrote in message
news:PXXye.27809$mr4.19841@trnddc05...
> It might be easier to just loop through each row and use the same
> conditions to determine what to do.
>
> (pseudo code)
>
> For rw = 1 to lastrow
> if cells(rw,4) = condition1 then
> ' your code
> elseif cells(rw,5)=condition2 then
> ' your code
> else
> ' anyother code you want
> end if
> next
>
>
> for copying -
> Sheets("MP").Range(????)=Sheets("MySheet").Range(rw,2)
> or
> Sheets("MP").Range(????).value=Sheets("MySheet").Range(rw,2).value
> or
> Sheets("MP").Range(????).text=Sheets("MySheet").Range(rw,2).text
>
>
> the trick is to define lastrow, and to define the row to paste to...
>
> write back if you need more...
>
> --
> steveB
>
> Remove "AYN" from email to respond
> "nat3ten" <nat3ten@hotmail.com> wrote in message
> news:1120684285.311919.47420@o13g2000cwo.googlegroups.com...
>>I am trying to write code which will automate a long process. If cells
>> in columns D or E are red(3) or gold(44), I want it to copy the info in
>> coulmn B (in the corresponding row)to another sheet called 'MP.' Is
>> there a way to do this?
>> Also, I thought I might note that the cells in columns D and E are red
>> or gold due to conditional formatting.
>> Thanks.
>>
>
>

This doesn't seem to work. Maybe I should give more background:
-I have 2 sheets: 'Mgmt Rev' and 'MP'
-If any rows in column F of 'Mgmt Rev' have: "FFP", "Conceptual",
"SOTA", "Very High", "0-50%", "Extreme", "New", "Poor", "Prewired",
"None", "Very High", "1", or "0-25%" then I want column D of that row
to be copied to coulmn B of sheet 'MP'

Is this possible?

Thanks for your help!


Orig email:
First - please leave any previous messages in your replys. Makes it
easier
to follow what has gone on before (my viewer is set to not display read

messages).


So let's walk through some code... note that any line with a '
represents a
note to the code.
Write back and let me know how it works. Also if you need more help...



"MySheet" is the name I will use to represent your data sheet (replace
with
the actual name)


In your Conditional Format you indicate that you have 2 conditions.
For simplicity I'll call condtion1 as something like formula is =3D
D1=3D
25
and condition 2 as something like formula is =3D E1 =3D 56


Using R1C1 notation
column D becomes =3D RC4=3D 25
column E becomes =3D RC5=3D 56


Note that Range("$D$5") =3D Cells(5,4)


=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=AD=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=AD=3D=3D
Sub TransfertoMP()
Dim rw as long, lrw as long, prw as long ' define variables for
row
numbers


' this finds the last used row in column A
lrw =3D Sheets("MySheet").Cells(Rows.C=ADOUNT, "A").End(xlUp).Row


' loop from 1st row to last row
For rw =3D 1 to lrw
' If cell in Column D =3D condition1
if cells(rw,4) =3D 25 then


' copy cell in MP column B to MP
Sheets("MP").Cells(prw,2) =3D Sheets("MySheet").Cells(rw,2)


' if cell in column E =3D condition2
elseif cells(rw,5)=3D56 then


' find first empty row on "MP"
prw =3D Sheets("MP").Cells(Rows.COUNT, "A").End(xlUp).Row + 1


' copy cell in MP column B to MP
Sheets("MP").Cells(prw,2) =3D Sheets("MySheet").Cells(rw,2)


' this one is only if you want a third option of something to
do
else
' any other code you want
end if
next


End Sub
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=AD=3D=3D=3D=3D=3D=3D=3D=3D


--=20
steveB=20


Remove "AYN" from email to respond

I am not good with arrays so I would put your list in a table of a hidden
sheet
Say this table is Worksheets("Sheet3").Range("A1:A13")

' This will copy the entire column
=======================================================
Dim x as Long, rw as Long

x = 0
For rw = 1 to 13
x = worksheetfunction.countif(Worksheets("'Mgmt Rev").Columns(6) + X
Next

If x = 0 then
MsgBox "No matches found"
Else
Worksheets("'Mgmt Rev").Columns(4).Copy _
Destination:= Worksheets("MP").Cells(1,2)
end if
======================================

If you want to do it cell by cell

Dim x as Long, rw as Long, lrw1 as Long, lrw2 as Long

x = 0
lrw1 = Worksheets("Mgmt Rev").Cells(Rows.COUNT, "F").End(xlUp).Row

For rw = 1 to lrw1
If worksheetfunction.countif(Worksheets("Sheet3").Columns(1) > 1 then
lrw2 = Worksheets("MP").Cells(Rows.COUNT, "B").End(xlUp).Row + 1
Worksheets("MP").Cells(lr2,2) = Worksheets("'Mgmt Rev").Cells(rw,4)
end if
Next

--
steveB

Remove "AYN" from email to respond
"nat3ten" <nat3ten@hotmail.com> wrote in message
news:1120858650.529808.21000@g44g2000cwa.googlegroups.com...
This doesn't seem to work. Maybe I should give more background:
-I have 2 sheets: 'Mgmt Rev' and 'MP'
-If any rows in column F of 'Mgmt Rev' have: "FFP", "Conceptual",
"SOTA", "Very High", "0-50%", "Extreme", "New", "Poor", "Prewired",
"None", "Very High", "1", or "0-25%" then I want column D of that row
to be copied to coulmn B of sheet 'MP'

Is this possible?

Thanks for your help!


Orig email:
First - please leave any previous messages in your replys. Makes it
easier
to follow what has gone on before (my viewer is set to not display read

messages).


So let's walk through some code... note that any line with a '
represents a
note to the code.
Write back and let me know how it works. Also if you need more help...



"MySheet" is the name I will use to represent your data sheet (replace
with
the actual name)


In your Conditional Format you indicate that you have 2 conditions.
For simplicity I'll call condtion1 as something like formula is =
D1=
25
and condition 2 as something like formula is = E1 = 56


Using R1C1 notation
column D becomes = RC4= 25
column E becomes = RC5= 56


Note that Range("$D$5") = Cells(5,4)


==============================*==============================*==
Sub TransfertoMP()
Dim rw as long, lrw as long, prw as long ' define variables for
row
numbers


' this finds the last used row in column A
lrw = Sheets("MySheet").Cells(Rows.C*OUNT, "A").End(xlUp).Row


' loop from 1st row to last row
For rw = 1 to lrw
' If cell in Column D = condition1
if cells(rw,4) = 25 then


' copy cell in MP column B to MP
Sheets("MP").Cells(prw,2) = Sheets("MySheet").Cells(rw,2)


' if cell in column E = condition2
elseif cells(rw,5)=56 then


' find first empty row on "MP"
prw = Sheets("MP").Cells(Rows.COUNT, "A").End(xlUp).Row + 1


' copy cell in MP column B to MP
Sheets("MP").Cells(prw,2) = Sheets("MySheet").Cells(rw,2)


' this one is only if you want a third option of something to
do
else
' any other code you want
end if
next


End Sub
==============================*========


--
steveB


Remove "AYN" from email to respond