More Excel Answers
Question Library
Ask a question about Excel
Volunteer
Experts of the Month
Expert Login
Awards
About Us
Tell friends
Link to Us
Disclaimer
|
| |
|
|
| |
| | | |
About Tom Ogilvy
Expertise
Worked with the program for many years - provided assistance on MS Excel Newsgroups since 1997. Have received the Microsoft MVP award annually since 1999.
I don't answer questions on using Excel in a browser
Since I have no way to test this. Prefer not to answer charting questions. I consider myself to be particularly knowledgeable about using VBA internal to Excel but have no problems with formulas and pivot tables either.
Experience
Have Used Excel for 15 - 20 years. Answered in excess of 70,000 Excel related questions in MS Excel newsgroups. Unless obvious, please specify whether you want a worksheet function or macro/VBA solution.
Education/Credentials
BS General Engineering (concentration in Industrial Engineering)
MS Operations Research Systems Analysis
| | |
| |
You are here: Experts > Computing/Technology > Microsoft Software > Excel > Copy Condtional formatted rows and paste in another worksheet
Excel - Copy Condtional formatted rows and paste in another worksheet
Expert: Tom Ogilvy - 11/4/2009
Question
QUESTION: Hi Tom,
I have a workbook for which I have used macro to find the difference between 2 cells (let's assume C & D) and conditional format on cell D when the value is not equal to cell C (I have used Yellow Color). What I want is to copy the whole row where the Conditional fomat has been applied and paste it to the next sheet. Since there will be more than one cell that will be conditionally formatted, It should copy the row and paste below one another in the next sheet. I'm sorry if i'm not clear about this. Please let me know so that I can explain it again
Thanks
Vicky
ANSWER: Vicky,
If you have used a macro to mark column D yellow, then I assume you changed the interior color of the cell and didn't actually apply a conditional format setting to the cell.
If so
Sub copyData()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim r1 As Range, cell As Range
Dim r As Range
Set sh1 = Worksheets("Sheet1") ' sheet with data
Set sh2 = Worksheets("Sheet2") ' sheet to copy to
Set r1 = sh1.Range(sh1.Cells(2, "D"), sh1.Cells(Rows.Count, "D").End(xlUp))
For Each cell In r1
If cell.Interior.ColorIndex = 6 Then
If r Is Nothing Then
Set r = cell
Else
Set r = Union(r, cell)
End If
End If
Next
If Not r Is Nothing Then
r.EntireRow.Copy sh2.Cells(2, "A")
Else
MsgBox "Nothing to copy"
End If
End Sub
Worked for me. It assumes the interior of the cells in column D for the rows to be copies are colored with a yellow color that carries a colorindex value of 6.
--
Regards,
Tom Ogilvy
---------- FOLLOW-UP ----------
QUESTION: Hi Tom,
Thanks for the swift reply. I am copy pasting the code that I used for the conditional formatting. It might look a bit atrocious :-), but since i'm very new to macro's, this is what I could come up with. There are about 800 rows right now which might increase or decrease, so all I need is for the code to check each cell in column D and if there is a conditional formatting applied based on the criteria, it should copy the complete row and paste it in the next sheet. This should do for all the cells which is formatted, and paste all the rows with conditionally formatted cell in the next sheet.
Sheets("Change in steps").Select
lastrow = Worksheets("Change in steps").Range("A65536").End(xlUp).Row
With Worksheets("Change in steps").Range("D2")
Range("D2").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
Formula1:="=C2"
With Selection.FormatConditions(1).Interior
.Color = 65535
End With
Selection.FormatConditions(1).StopIfTrue = True
Selection.Copy
Range("D3").Select
Range(Selection, "D3:D" & lastrow&).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("D2").Select
Application.CutCopyMode = False
End With
Range("A2").Select
Please advice
Cheers
Vicky
Answer
Vicky,
Since you can't check if conditional formatting is being applied by using any built in attribute or property, it is best to check the same condition as the conditional formatting. Chip Pearson has written a hugh amount of code that demonstrates how to do this - but the task is daunting. since you have a simple condition and you know what it is, it is easiest to just have your code check the same condition.
Sub copyData()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim r1 As Range, cell As Range
Dim r As Range, cell1 as Range
Set sh1 = Worksheets("Change in steps") ' sheet with data
Set sh2 = Worksheets("Sheet2") ' sheet to copy to
Set r1 = sh1.Range(sh1.Cells(2, "D"), sh1.Cells(Rows.Count, "D").End(xlUp))
For Each cell In r1
cell1 = cell.offset(0,-1)
If cell.value <> cell1.Value Then
If r Is Nothing Then
Set r = cell
Else
Set r = Union(r, cell)
End If
End If
Next
If Not r Is Nothing Then
r.EntireRow.Copy sh2.Cells(2, "A")
Else
MsgBox "Nothing to copy"
End If
End Sub
here is a link to Chip's page for the more abundant approach:
http://www.cpearson.com/excel/CFColors.htm
--
Regards,
Tom Ogilvy
Ask a Question
|
|
