Copy/Paste Cells w/ Conditional Formatting
Copy/Paste Cells w/ Conditional Formatting
Copy/Paste Cells w/ Conditional Formatting
Copy/Paste Cells w/ Conditional Formatting
Copy/Paste Cells w/ Conditional Formatting
Copy/Paste Cells w/ Conditional Formatting Copy/Paste Cells w/ Conditional Formatting Copy/Paste Cells w/ Conditional Formatting Copy/Paste Cells w/ Conditional Formatting Copy/Paste Cells w/ Conditional Formatting Copy/Paste Cells w/ Conditional Formatting Copy/Paste Cells w/ Conditional Formatting Copy/Paste Cells w/ Conditional Formatting
Copy/Paste Cells w/ Conditional Formatting Copy/Paste Cells w/ Conditional Formatting
Copy/Paste Cells w/ Conditional Formatting
Go Back  Xtreme Visual Basic Talk > > > > Copy/Paste Cells w/ Conditional Formatting


Reply
 
Thread Tools Display Modes
  #1  
Old 05-02-2017, 03:51 PM
cacody cacody is offline
Junior Contributor
 
Join Date: Apr 2011
Location: Scottsdale AZ
Posts: 323
Default Copy/Paste Cells w/ Conditional Formatting


I have two ranges where I compare values. Let's call them range A and range B. I use conditional formatting to highlight those values in range A that are not in range B. Range B has all historical values, so that whenever a new value shows up in range A (not found in range B) then it is highlighted via conditional formatting.

Here is a sub that loops through range A, finds highlighted cells, and copies those rows to the end of range B so that it is kept up to date. However, it doesn't work. Excel doesn't work the same with conditionally formatted cells, apparently.

Code:
Sub FindNewHL()
Dim lastRow As Long, r As Long, x As Range, wsA As Worksheet, wsB As Worksheet, lstrw As Long
Application.ScreenUpdating = False
Set wsA = Sheets("Sheet1")
Set wsB = Sheets("Sheet3")

lastRow = wsA.Cells(Rows.Count, "A").End(xlUp).Row
lstrw = wsB.Cells(Rows.Count, "A").End(xlUp).Row
r = lstrw + 1

For Each x In wsA.Range("H2:H" & lastRow)
    If x.Interior.Pattern <> xlNone Then   'this is the issue here
        x.Copy Destination:=wsB.Range("A" & r)
        wsB.Range("A" & r).FormatConditions.Delete
        wsB.Range("A" & r).Interior.Pattern = xlNone
        x.Offset(0, 1).Copy Destination:=wsB.Range("B" & r)
        r = r + 1
    End If
Next x
End Sub
The range on wsA is range A, and the range on wsB is range B.
I have tried Interior. Pattern, Interior.Color, Interior.ColorIndex, RGB(X,Y,Z), Font.Bold, and FormatConditions.

I think I'm close but could use help with this. Thanks.
Reply With Quote
  #2  
Old 05-02-2017, 09:25 PM
Kluz's Avatar
KluzCopy/Paste Cells w/ Conditional Formatting Kluz is offline
Sapience.Aquire

Super Moderator
* Expert *
 
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,445
Default

You will need to use the same criteria that your conditional formatting uses when changing the cell rather than the ".Pattern <> None" such as: this cell's value <> that cell's value.
__________________
No the other right mouse click
Reply With Quote
  #3  
Old 05-03-2017, 02:19 PM
cacody cacody is offline
Junior Contributor
 
Join Date: Apr 2011
Location: Scottsdale AZ
Posts: 323
Default

Ah Ha! Great suggestion, Kluz. Here is the revised code that works!

Code:
Sub FindNewHL()
Dim cell As Range, rng As Range, wsA As Worksheet, wsB As Worksheet, r As Integer, lstrw As Integer
Dim mycel, rng2 As Range

Application.ScreenUpdating = False

Set rng = Range("List2")
Set rng2 = Range("OldList")
Set wsB = Sheets("Sheet3")

lstrw = wsB.Cells(Rows.Count, "A").End(xlUp).Row

r = lstrw + 1
For Each cell In rng
    mycel = Application.WorksheetFunction.CountIf(rng2, cell)
    If mycel = 0 Then
        cell.Copy Destination:=wsB.Range("A" & r)
        wsB.Range("A" & r).FormatConditions.Delete
        wsB.Range("A" & r).Interior.Pattern = xlNone
        cell.Offset(0, 1).Copy Destination:=wsB.Range("B" & r)
        r = r + 1
    End If
Next cell
End Sub
I was using the CountIf function in Conditional Formatting to shade those cells where there was no match between the ranges - [=COUNTIF(OldList,H2) = 0], etc. The function works here as you suggested. The ranges are only about 550 rows, so no need for variant arrays - it works fast enough.

I kept the removal of the shading after copy. Thanks again.
Reply With Quote
  #4  
Old 05-03-2017, 08:10 PM
Kluz's Avatar
KluzCopy/Paste Cells w/ Conditional Formatting Kluz is offline
Sapience.Aquire

Super Moderator
* Expert *
 
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,445
Default

You're welcome.
__________________
No the other right mouse click
Reply With Quote
Reply

Tags
range, lastrow, conditional, cells, values, formatting, wsb, lstrw, wsa, set, a.endxlup.row, wsb.rangea, xlnone, worksheet, highlighted, interior.colorindex, font.bold, rgbx, y, z, formatconditions, wsa.cellsrows.count, close, wsb.cellsrows.count, interior.color, r.formatconditions.delete, destination=wsb.rangeb


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Copy/Paste Cells w/ Conditional Formatting
Copy/Paste Cells w/ Conditional Formatting
Copy/Paste Cells w/ Conditional Formatting Copy/Paste Cells w/ Conditional Formatting
Copy/Paste Cells w/ Conditional Formatting
Copy/Paste Cells w/ Conditional Formatting
Copy/Paste Cells w/ Conditional Formatting Copy/Paste Cells w/ Conditional Formatting Copy/Paste Cells w/ Conditional Formatting Copy/Paste Cells w/ Conditional Formatting Copy/Paste Cells w/ Conditional Formatting Copy/Paste Cells w/ Conditional Formatting Copy/Paste Cells w/ Conditional Formatting
Copy/Paste Cells w/ Conditional Formatting
Copy/Paste Cells w/ Conditional Formatting
 
Copy/Paste Cells w/ Conditional Formatting
Copy/Paste Cells w/ Conditional Formatting
 
-->