How do I add this formula to my Macro

[Jack-P-Winner]

I have a Macro that every time it does a new search it deletes the data from the "values found" sheet and places the new data on the new "values found" sheet. It is working great except 1 thing. It deletes my Conditional formatting every time it does a new search. I either need to figure out how to stop this from happening or add all my color coding to my Macro. I would appreciate any suggestions

Thanks!!

Here is the type formula I want to add. There are well over 100 rules like this

=AND(O2=2,O3=4,O4=4, O5=4, O6=4, O7=4, O8=4, O9=6) =$O$2:$O$2498

Here is my Macro

Sub CopyMatches4()
    Dim rngC As Range
    Dim i As Long
    Dim rStart As Long
    Dim rFinish As Long
    Dim rngPattern As Range
    Dim shtWork As Worksheet
    Dim shtPaste As Worksheet
    Dim iCnt As Integer
    Dim iBuffer As Integer
 
    iBuffer = 10  'Rows above and below to copy, and to skip between blocks

    iCnt = 0

    Set shtWork = ActiveSheet

    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Found Values").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    Worksheets.Add After:=Worksheets(Worksheets.Count)
    Set shtPaste = Worksheets(Worksheets.Count)
    shtPaste.Name = "Found Values"

    shtWork.Activate

    Set rngPattern = Application.InputBox("Select the pattern range(s)", Type:=8)

    rStart = rngPattern.Cells(rngPattern.Cells.Count).Row + 1
    rFinish = rngPattern.Parent.UsedRange.Cells(rngPattern.Parent.UsedRange.Cells.Count).Row

    For i = rStart - rngPattern.Cells(1).Row To rFinish
        For Each rngC In rngPattern
            If rngC.Offset(i).Value <> rngC.Value Then
                GoTo CheckNext
            End If
        Next rngC
        rngPattern.Offset(i).BorderAround xlContinuous, xlThick
        rngPattern.Offset(i - iBuffer).Resize(rngPattern.Rows.Count + iBuffer * 2).EntireRow.Copy _
        shtPaste.Range("A2").Offset(iCnt * (rngPattern.Rows.Count + iBuffer * 2)).Offset(iCnt * iBuffer).Resize(rngPattern.Rows.Count + iBuffer * 2).EntireRow
        iCnt = iCnt + 1
        rngPattern.Offset(i).Select
CheckNext:
    Next i
 
    With shtPaste
        .Range("B:B,D:D,F:F,H:H,K:K,N:N,P:P,S:S,V:V,AA:AA,AD:AD,AG:AG,AJ:AJ").ColumnWidth = 0.35
        .Range("I:J,L:M,O:O,Q:R,T:U").ColumnWidth = 3.5
        .Range("W:Z,AB:AC").ColumnWidth = 3#
        .Range("E:E").ColumnWidth = 18.5
        .Range("AE:AF,AH:AI").ColumnWidth = 5
    End With
End Sub

 

[Jack-P-Winner]

Here is an example of the color coding under column "O"

 

[Smallman]

Hi Jack

Just a suggestion but why not use the Values Found sheet as a template. Instead of deleting the whole sheet just clear the contents and put the new data in that sheet. That would seem an easy work around.

Take care

Smallman

 

[Jack-P-Winner]

Good idea, would I just change the "delete" to "clear"?

On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Found Values").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

 

[Smallman]

No change it to

.clearcontents

and you will need to do it like this

cells.clearcontents

or specify a range.

Take care

Smallman

 

[Jack-P-Winner]

It shows up as an error?

 On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Found Values")cells.clearcontents
    Application.DisplayAlerts = True
    On Error GoTo 0

 

[Smallman]

where is the .
before the cells?

 

[Jack-P-Winner]

I am not sure I understand your question before and after cells. Here is a snap shot of the values found sheet and it will go down 2500 rows sometimes

 

[Smallman]

Jack

Change:

Worksheets("Found Values")Cells.ClearContents

to

Worksheets("Found Values").Cells.ClearContents

where is the . before the cells - see above.

Take care

Smallman

 

[Jack-P-Winner]

I got it working but it still removes the conditional formatting

 

[Smallman]

Well without a file that can't be tested except in a test environment. Here is what I do when i want to test a theory - put some dummy data in a fresh sheet, write a snippet of code on the back of some simple conditional formatting and run it. If that works I know that the theory is good and it is probably my spreadsheet that is bad.

Here is an example Column A2 to A15 any cell greater than 1 highlight in orange. Some coding.

Sub testo()
    Cells.ClearContents
End Sub

Run code on file - what happens when I put 1 in A2 - turns orange.

Theory sound. Spreadsheet probably the issue.

Smallman