Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formating for the whole row, based on one cell value | Excel Discussion (Misc queries) | |||
copy rows based on cell colour | Excel Programming | |||
copy rows based on cell colour | Excel Programming | |||
Conditional Formating based on a different cell | Excel Discussion (Misc queries) | |||
Conditional Formating based on another cell | Excel Discussion (Misc queries) |