Need comparison to be done

Copper Contributor

Below code helps in getting the data in other columns

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.CountLarge = 1 Then
If Not Intersect(Target, Range("D2:D43")) Is Nothing Then
Range("M3").Value = Target.Value
Range("M2").Value = Target.Offset(0, 1).Value
End If
End If
End Sub

 

Now the value which is coming in M2 should be saved in an another cell. Then compare the next value with the current value if it is High of M2 should go in S2 and Low of that goes in T2 with time stamp either in another cell or the same cell.

 

I need this to be working multiple cells like:

M3 high should go to S3 and Low of that goes in T3.

P3 high should go to S4 and Low of that goes in T4.

P4 high should go to S5 and Low of that goes in T6.

 

and so on.

 

When we close the file the data should be removed and it should start from zero.

 

TIA

8 Replies

@tusharkaushik 

I don;'t understand. What do P3 and P4 have to do with this?

And why T6 and not T5?

I have different value coming in P3 and P4 which will be showing is T6 and not T5.

Basically i have six different tables and the data is showing like
M2 & M3 then P2, P3
There is a one row space then
the data again M5 & M6 and P5 & P6.

 

I am ok if we remove the row space so now the data will in the columns and they will be like

 

M2 & M3 then P2 & P3

M4 & M5 then P4 & P5

M6 & M7 then P6 & P7

 

Now for the other 3 tables they are on the right hand side. The data is showing like
X2 & X3 then AA2, AA3
X4 & X5 then AA4 & AA5.
X6 & X7 then AA6 & AA7.

@tusharkaushik 

Please provide detailed information about the six different tables, and/or attach a sample workbook.

Please find the excel attached.

 

when you scroll down with arrow key in column D the values in column M and Z gets changed

 

Now whatever is the high of CP for Sq2 goes to S2 and Low to T2, also the high of T goes to U2 and low should be in V2

 

Same for Sq high of CP for Sq goes to S3 and Low to T3, also the high of T goes to U3 and low should be in V3.

 

This has to for every table i don't mind to make it easier we have to remove the space between these.

 

Also when we reopen the sheet the values of high and low should be back to zero, without the macro as i have to implement in other sheets.

 

Does anyone else have an idea? I don't understand this at all. Thanks!

Basically we have the compare the value of m2 with the next coming value if it is higher than it should go to S2 if it's lower it should go to T2.
I have created code which is coping the data in another worksheet but the problem is it is working perfectly till picking the data of M2 and M3 but not showing the data for M5 and M6

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("M2").Address Then
Dim intLastRow As Long
intLastRow = Sheet2.Cells(Sheet2.Rows.Count, "B").End(xlUp).Row
Sheet2.Cells(intLastRow + 1, "B") = Target.Value

ElseIf Target.Address = Range("M3").Address Then
Dim intLastRow2 As Long
intLastRow2 = Sheet2.Cells(Sheet2.Rows.Count, "C").End(xlUp).Row
Sheet2.Cells(intLastRow2 + 1, "C") = Target.Value

End If

If Target.Address = Range("M5").Address Then
Dim intLastRow3 As Long
intLastRow3 = Sheet2.Cells(Sheet2.Rows.Count, "D").End(xlUp).Row
Sheet2.Cells(intLastRow2 + 1, "D") = Target.Value

ElseIf Target.Address = Range("M6").Address Then
Dim intLastRow4 As Long
intLastRow4 = Sheet2.Cells(Sheet2.Rows.Count, "E").End(xlUp).Row
Sheet2.Cells(intLastRow4 + 1, "E") = Target.Value

End If
End Sub