Help to find higher and lower value theen copy to another sheet

[sbv1986]

Hi all
I have workbook with 4 sheets("datanew", "dataold","higher", "lower"), now I want to find higher/lower value then copy to sheets("higher/lower") with conditions that:

With sheets("datanew")
lastrow = .Cells(Rows.Count, 12).End(xlUp).Row
lastcolumn = .Cells(1, Columns.Count).End(xlToLeft).Column
     for i =3 to lastrow
          for j =21 to lastrow
             if .cells(i,12).value + .cells(i,13).value < .cells(i,j).value then
                  sheets("higher").range(A).value = .cells(1,j).value
                  sheets("higher").range(B).value = .cells(i,j).value
             else
             if .cells(i,12).value - .cells(i,13).value > .cells(i,j).value then
                  sheets("lower").range(A).value = .cells(1,j).value
                  sheets("lower").range(B).value = .cells(i,j).value
             end if
     next j
     next i
End with

Please help me to do this with VBa code or Pivot table, many thanks./.

 

[pbornemeier]

I would use conditional formatting on the datanew worksheet comparing matching cells to the dataold worksheet and producing green if data new was higher and red if it was lower.

Option Explicit

Sub CopyDatanewToHiOrLow()

    Dim rngCell As Range
    Dim rngData As Range
    
    'Create a conditional format for the rngData cells on newdata that fills the cells
    '  with rgbLime (0,255,0) if the new data is greater than the old data and
    '  rgbRed (0,0,255) if the old data is greater than the new data.
    
    With Worksheets("datanew")
        lastrow = .Cells(Rows.Count, 12).End(xlUp).Row
        lastcolumn = .Cells(1, Columns.Count).End(xlToLeft).Column
    
        'You referenced lastrow twice...I made a guess which one should be lastcolumn
        Set rngData = Worksheets("datanew").Range(.Cells(3, 21), .Cells(lastrow, lastcolumn))
        
        For Each rngCell In rngData.Cells
            If rngCell.DisplayFormat.Interior.Color = rgbRed Then
                Worksheets("lower").Range(rngCell.Address).Value = _
                    .Range(rngCell.Address).Value
            ElseIf rngCell.DisplayFormat.Interior.Color = rgbLime Then
                Worksheets("higher").Range(rngCell.Address).Value = _
                    .Range(rngCell.Address).Value
            End If
        Next
    End With
End Sub

 

[DanteAmor]

Hi all
I have workbook with 4 sheets("datanew", "dataold","higher", "lower"), now I want to find higher/lower value then copy to sheets("higher/lower") with conditions that:

With sheets("datanew")
lastrow = .Cells(Rows.Count, 12).End(xlUp).Row
lastcolumn = .Cells(1, Columns.Count).End(xlToLeft).Column
     for i =3 to lastrow
          for j =21 to [COLOR=#ff0000]lastrow[/COLOR]
             if .cells(i,12).value + .cells(i,13).value < .cells(i,j).value then
                  sheets("higher").range(A).value = .cells(1,j).value
                  sheets("higher").range(B).value = .cells(i,j).value
             else
             if .cells(i,12).value - .cells(i,13).value > .cells(i,j).value then
                  sheets("lower").range(A).value = .cells(1,j).value
                  sheets("lower").range(B).value = .cells(i,j).value
             end if
     next j
     next i
End with

Please help me to do this with VBa code or Pivot table, many thanks./.

In this line "for j =21 to lastrow" must be "lastcolumn"?


You could put an example with data of what you have and what you expect of result.

 

[sbv1986]

Thanks @DanteAmor , that's last column as you said. It's my mistake when type

 

[DanteAmor]

if .cells(i,12).value + .cells(i,13).value < .cells(i,j).value then
                  sheets("higher").range(A).value = .cells(1,j).value
                  sheets("higher").range(B).value = .cells(i,j).value
             [B][COLOR=#ff0000]else[/COLOR][/B]
             if .cells(i,12).value - .cells(i,13).value > .cells(i,j).value then
                  sheets("lower").range(A).value = .cells(1,j).value
                  sheets("lower").range(B).value = .cells(i,j).value
             end if

Also the Else must be End If