copy rows based on cell colour by conditional formating

[K]

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

 

[Joel]

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

 

[Ron de Bruin]

Hi K

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

 

[K]

is there any macro for this

Hi K

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

 

[K]

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

 

[JLGWhiz]

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

 

[K]

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






- Show quoted text -

Hi JLG, please state the macro thanks

 

[JLGWhiz]

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]

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






- Show quoted text -

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