copy rows based on cell colour by conditional formating
 

Hi, i get "Interior.Colorindex = 3" or Red colour in coloumn B cells
by conditional formatting. I have put formula in conditional
formatting that when if value of cell in coloumn B is True by formula
then cell get Red colour. One of my online friend send me the macro
(please see below) which work fine but little problem that instead of
coping only Red coloured cells by conditional formatting it copies all
sheet1 data to sheet2. I want macro to copy only those cells rows
which got Red colour by conditional formatting. and i dont want
entire row just from cell A to cell F. Please any body can help as i
am doing project for my job and this will be very helpful. " I know
that there are lot of my friends out there are very good in macros"
Please help. Thanks..........

Sub cpyColr()
Dim c As Range
lastRw = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
For Each c In Worksheets(1).Range("B2:B" & lastRw)
If c.FormatConditions(1).Interior.ColorIndex = 3 Then
lstRw2 = Worksheets(2).Range("A65536").End(xlUp).Row
cRng = c.Address
Worksheets(1).Range("A" & Range(cRng).Row & ":F" &
Range(cRng).Row).Copy _
Worksheets(2).Range("A" & lstRw2 + 1)
End If
Next
End Sub

copy rows based on cell colour by conditional formating
 

See website below. You can't directly view the color set by conditional
formating. You have to get the setting of the conditional formating and
recalculate the formula in Condittional formating using VBA.


http://www.cpearson.com/excel/CFColors.htm


"K" wrote:

Hi, i get "Interior.Colorindex = 3" or Red colour in coloumn B cells
by conditional formatting. I have put formula in conditional
formatting that when if value of cell in coloumn B is True by formula
then cell get Red colour. One of my online friend send me the macro
(please see below) which work fine but little problem that instead of
coping only Red coloured cells by conditional formatting it copies all
sheet1 data to sheet2. I want macro to copy only those cells rows
which got Red colour by conditional formatting. and i dont want
entire row just from cell A to cell F. Please any body can help as i
am doing project for my job and this will be very helpful. " I know
that there are lot of my friends out there are very good in macros"
Please help. Thanks..........

Sub cpyColr()
Dim c As Range
lastRw = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
For Each c In Worksheets(1).Range("B2:B" & lastRw)
If c.FormatConditions(1).Interior.ColorIndex = 3 Then
lstRw2 = Worksheets(2).Range("A65536").End(xlUp).Row
cRng = c.Address
Worksheets(1).Range("A" & Range(cRng).Row & ":F" &
Range(cRng).Row).Copy _
Worksheets(2).Range("A" & lstRw2 + 1)
End If
Next
End Sub

copy rows based on cell colour by conditional formating
 

Hi K

With EasyFilter you can also do this
http://www.rondebruin.nl/easyfilter.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"K" wrote in message ...
Hi, i get "Interior.Colorindex = 3" or Red colour in coloumn B cells
by conditional formatting. I have put formula in conditional
formatting that when if value of cell in coloumn B is True by formula
then cell get Red colour. One of my online friend send me the macro
(please see below) which work fine but little problem that instead of
coping only Red coloured cells by conditional formatting it copies all
sheet1 data to sheet2. I want macro to copy only those cells rows
which got Red colour by conditional formatting. and i dont want
entire row just from cell A to cell F. Please any body can help as i
am doing project for my job and this will be very helpful. " I know
that there are lot of my friends out there are very good in macros"
Please help. Thanks..........

Sub cpyColr()
Dim c As Range
lastRw = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
For Each c In Worksheets(1).Range("B2:B" & lastRw)
If c.FormatConditions(1).Interior.ColorIndex = 3 Then
lstRw2 = Worksheets(2).Range("A65536").End(xlUp).Row
cRng = c.Address
Worksheets(1).Range("A" & Range(cRng).Row & ":F" &
Range(cRng).Row).Copy _
Worksheets(2).Range("A" & lstRw2 + 1)
End If
Next
End Sub

copy rows based on cell colour by conditional formating
 

is there any macro for this

Ron de Bruin wrote:
Hi K

With EasyFilter you can also do this
http://www.rondebruin.nl/easyfilter.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"K" wrote in message ...
Hi, i get "Interior.Colorindex = 3" or Red colour in coloumn B cells
by conditional formatting. I have put formula in conditional
formatting that when if value of cell in coloumn B is True by formula
then cell get Red colour. One of my online friend send me the macro
(please see below) which work fine but little problem that instead of
coping only Red coloured cells by conditional formatting it copies all
sheet1 data to sheet2. I want macro to copy only those cells rows
which got Red colour by conditional formatting. and i dont want
entire row just from cell A to cell F. Please any body can help as i
am doing project for my job and this will be very helpful. " I know
that there are lot of my friends out there are very good in macros"
Please help. Thanks..........

Sub cpyColr()
Dim c As Range
lastRw = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
For Each c In Worksheets(1).Range("B2:B" & lastRw)
If c.FormatConditions(1).Interior.ColorIndex = 3 Then
lstRw2 = Worksheets(2).Range("A65536").End(xlUp).Row
cRng = c.Address
Worksheets(1).Range("A" & Range(cRng).Row & ":F" &
Range(cRng).Row).Copy _
Worksheets(2).Range("A" & lstRw2 + 1)
End If
Next
End Sub

copy rows based on cell colour by conditional formating
 

Thanks for recomendings but as there will be more people using my
spreadsheet and a macro will do much help for me. Please if any body
can find a macro for me thanks

K wrote:
is there any macro for this

Ron de Bruin wrote:
Hi K

With EasyFilter you can also do this
http://www.rondebruin.nl/easyfilter.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"K" wrote in message ...
Hi, i get "Interior.Colorindex = 3" or Red colour in coloumn B cells
by conditional formatting. I have put formula in conditional
formatting that when if value of cell in coloumn B is True by formula
then cell get Red colour. One of my online friend send me the macro
(please see below) which work fine but little problem that instead of
coping only Red coloured cells by conditional formatting it copies all
sheet1 data to sheet2. I want macro to copy only those cells rows
which got Red colour by conditional formatting. and i dont want
entire row just from cell A to cell F. Please any body can help as i
am doing project for my job and this will be very helpful. " I know
that there are lot of my friends out there are very good in macros"
Please help. Thanks..........

Sub cpyColr()
Dim c As Range
lastRw = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
For Each c In Worksheets(1).Range("B2:B" & lastRw)
If c.FormatConditions(1).Interior.ColorIndex = 3 Then
lstRw2 = Worksheets(2).Range("A65536").End(xlUp).Row
cRng = c.Address
Worksheets(1).Range("A" & Range(cRng).Row & ":F" &
Range(cRng).Row).Copy _
Worksheets(2).Range("A" & lstRw2 + 1)
End If
Next
End Sub

copy rows based on cell colour by conditional formating
 

Here is a modified version that should only select those rows that have
conditional format with interior color = red.

"K" wrote:

Thanks for recomendings but as there will be more people using my
spreadsheet and a macro will do much help for me. Please if any body
can find a macro for me thanks

K wrote:
is there any macro for this

Ron de Bruin wrote:
Hi K

With EasyFilter you can also do this
http://www.rondebruin.nl/easyfilter.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"K" wrote in message ...
Hi, i get "Interior.Colorindex = 3" or Red colour in coloumn B cells
by conditional formatting. I have put formula in conditional
formatting that when if value of cell in coloumn B is True by formula
then cell get Red colour. One of my online friend send me the macro
(please see below) which work fine but little problem that instead of
coping only Red coloured cells by conditional formatting it copies all
sheet1 data to sheet2. I want macro to copy only those cells rows
which got Red colour by conditional formatting. and i dont want
entire row just from cell A to cell F. Please any body can help as i
am doing project for my job and this will be very helpful. " I know
that there are lot of my friends out there are very good in macros"
Please help. Thanks..........

Sub cpyColr()
Dim c As Range
lastRw = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
For Each c In Worksheets(1).Range("B2:B" & lastRw)
If c.FormatConditions(1).Interior.ColorIndex = 3 Then
lstRw2 = Worksheets(2).Range("A65536").End(xlUp).Row
cRng = c.Address
Worksheets(1).Range("A" & Range(cRng).Row & ":F" &
Range(cRng).Row).Copy _
Worksheets(2).Range("A" & lstRw2 + 1)
End If
Next
End Sub

copy rows based on cell colour by conditional formating
 

On 15 Dec, 16:51, JLGWhiz wrote:
Here is a modified version that should only select those rows that have
conditional format with interior color = red.



"K" wrote:
Thanks for recomendings but as there will be more people using my
spreadsheet and a macro will do much help for me. Please if any body
can find a macro for me thanks

K wrote:
is there any macro for this

Ron de Bruin wrote:
Hi K

With EasyFilter you can also do this
http://www.rondebruin.nl/easyfilter.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"K" wrote in ...
Hi, i get "Interior.Colorindex = 3" or Red colour in coloumn B cells
by conditional formatting. I have put formula in conditional
formatting that when if value of cell in coloumn B is True by formula
then cell get Red colour. One of my online friend send me the macro
(please see below) which work fine but little problem that instead of
coping only Red coloured cells by conditional formatting it copies all
sheet1 data to sheet2. I want macro to copy only those cells rows
which got Red colour by conditional formatting. and i dont want
entire row just from cell A to cell F. Please any body can help as i
am doing project for my job and this will be very helpful. " I know
that there are lot of my friends out there are very good in macros"
Please help. Thanks..........

Sub cpyColr()
Dim c As Range
lastRw = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
For Each c In Worksheets(1).Range("B2:B" & lastRw)
If c.FormatConditions(1).Interior.ColorIndex = 3 Then
lstRw2 = Worksheets(2).Range("A65536").End(xlUp).Row
cRng = c.Address
Worksheets(1).Range("A" & Range(cRng).Row & ":F" &
Range(cRng).Row).Copy _
Worksheets(2).Range("A" & lstRw2 + 1)
End If
Next
End Sub- Hide quoted text -

- Show quoted text -

Hi JLG, please state the macro thanks

copy rows based on cell colour by conditional formating
 

Sorry about that:

Sub cpyColr()
Dim c As Range
lastRw = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
For Each c In Worksheets(1).Range("B2:B" & lastRw)
If c.FormatConditions.Count 0 Then
If c.FormatConditions(1).Interior.ColorIndex = 3 Then
lstRw2 = Worksheets(2).Range("A65536").End(xlUp).Row
cRng = c.Address
Worksheets(1).Range("A" & Range(cRng).Row & ":F" & _
Range(cRng).Row).Copy Worksheets(2).Range("A" & lstRw2 + 1)
End If
End If
Next
End Sub


"K" wrote:

On 15 Dec, 16:51, JLGWhiz wrote:
Here is a modified version that should only select those rows that have
conditional format with interior color = red.



"K" wrote:
Thanks for recomendings but as there will be more people using my
spreadsheet and a macro will do much help for me. Please if any body
can find a macro for me thanks

K wrote:
is there any macro for this

Ron de Bruin wrote:
Hi K

With EasyFilter you can also do this
http://www.rondebruin.nl/easyfilter.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"K" wrote in ...
Hi, i get "Interior.Colorindex = 3" or Red colour in coloumn B cells
by conditional formatting. I have put formula in conditional
formatting that when if value of cell in coloumn B is True by formula
then cell get Red colour. One of my online friend send me the macro
(please see below) which work fine but little problem that instead of
coping only Red coloured cells by conditional formatting it copies all
sheet1 data to sheet2. I want macro to copy only those cells rows
which got Red colour by conditional formatting. and i dont want
entire row just from cell A to cell F. Please any body can help as i
am doing project for my job and this will be very helpful. " I know
that there are lot of my friends out there are very good in macros"
Please help. Thanks..........

Sub cpyColr()
Dim c As Range
lastRw = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
For Each c In Worksheets(1).Range("B2:B" & lastRw)
If c.FormatConditions(1).Interior.ColorIndex = 3 Then
lstRw2 = Worksheets(2).Range("A65536").End(xlUp).Row
cRng = c.Address
Worksheets(1).Range("A" & Range(cRng).Row & ":F" &
Range(cRng).Row).Copy _
Worksheets(2).Range("A" & lstRw2 + 1)
End If
Next
End Sub- Hide quoted text -

- Show quoted text -

Hi JLG, please state the macro thanks

copy rows based on cell colour by conditional formating
 

On 15 Dec, 18:29, JLGWhiz wrote:
Sorry about that:

Sub cpyColr()
Dim c As Range
lastRw = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
For Each c In Worksheets(1).Range("B2:B" & lastRw)
If c.FormatConditions.Count 0 Then
If c.FormatConditions(1).Interior.ColorIndex = 3 Then
lstRw2 = Worksheets(2).Range("A65536").End(xlUp).Row
cRng = c.Address
Worksheets(1).Range("A" & Range(cRng).Row & ":F" & _
Range(cRng).Row).Copy Worksheets(2).Range("A" & lstRw2 + 1)
End If
End If
Next
End Sub



"K" wrote:
On 15 Dec, 16:51, JLGWhiz wrote:
Here is a modified version that should only select those rows that have
conditional format with interior color = red.

"K" wrote:
Thanks for recomendings but as there will be more people using my
spreadsheet and a macro will do much help for me. Please if any body
can find a macro for me thanks

K wrote:
is there any macro for this

Ron de Bruin wrote:
Hi K

With EasyFilter you can also do this
http://www.rondebruin.nl/easyfilter.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"K" wrote in ...
Hi, i get "Interior.Colorindex = 3" or Red colour in coloumn B cells
by conditional formatting. I have put formula in conditional
formatting that when if value of cell in coloumn B is True by formula
then cell get Red colour. One of my online friend send me the macro
(please see below) which work fine but little problem that instead of
coping only Red coloured cells by conditional formatting it copies all
sheet1 data to sheet2. I want macro to copy only those cells rows
which got Red colour by conditional formatting. and i dont want
entire row just from cell A to cell F. Please any body can help as i
am doing project for my job and this will be very helpful. " I know
that there are lot of my friends out there are very good in macros"
Please help. Thanks..........

Sub cpyColr()
Dim c As Range
lastRw = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
For Each c In Worksheets(1).Range("B2:B" & lastRw)
If c.FormatConditions(1).Interior.ColorIndex = 3 Then
lstRw2 = Worksheets(2).Range("A65536").End(xlUp).Row
cRng = c.Address
Worksheets(1).Range("A" & Range(cRng).Row & ":F" &
Range(cRng).Row).Copy _
Worksheets(2).Range("A" & lstRw2 + 1)
End If
Next
End Sub- Hide quoted text -

- Show quoted text -

Hi JLG, please state the macro thanks- Hide quoted text -

- Show quoted text -

Thanks you very much JLG & Ron. You guys are very good in macros