I have a sheet where data is posted from other sheets from column A to column J starting from row 2,
as per this sample. I need a code to check each row (that is not highlighted- as this will take long with more data added) where the data matches from column C to column F, if the data match as in row 2 and row 3, then the quantity in column G must be checked against the quantity in the first row of the matching rows, if they match, then the rows from A:J must be highlighted in Yellow, if the quantity is less than the first matching row, then the rows must not be highlighted, if the quantity is more than the first matching row, then the rows must be highlighted in Red. Therefore in the above data, rows (2 and 3) and (9 and 10) must be highlighted in Yellow as they match the criteria. Rows 4 and 5 must be highlighted red as the quantity in row 5 is more than that in row 4. Then Rows 6, 7 and 8 in this sample must be highlighted in yellow, because rows 7 and 8 added is equal to the quantity in row 6, the same criteria must be applied even when there are more than 1 matching row where the data matches from column C to column F. I have somewhat managed to get to a point where the rows are highlighted Yellow and red but not when there are more than 2 rows when the quantities must be added and then matched to the first matching row, to either be highlighted Yellow, Red or not be highlighted.
Here is a sample of the code to where I managed to get it:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim destSheet As Worksheet
Dim destLastRow As Long
Dim i As Long
Dim j As Long
Dim orderNum As String
Set destSheet = ThisWorkbook.Worksheets("Jaburhisa Orders")
destLastRow = destSheet.Range("C" & destSheet.Rows.Count).End(xlUp).Row
For i = 2 To destLastRow
If Not destSheet.Rows(i).Interior.Color = vbYellow And Not destSheet.Rows(i).Interior.Color = vbRed And destSheet.Range("C" & i).Value <> "" Then
orderNum = destSheet.Range("C" & i).Value
For j = i + 1 To destLastRow
If Not destSheet.Rows(j).Interior.Color = vbYellow And Not destSheet.Rows(j).Interior.Color = vbRed And destSheet.Range("C" & j).Value <> "" Then
If destSheet.Range("C" & j).Value = orderNum And _
destSheet.Range("D" & j).Value = destSheet.Range("D" & i).Value And _
destSheet.Range("E" & j).Value = destSheet.Range("E" & i).Value And _
destSheet.Range("F" & j).Value = destSheet.Range("F" & i).Value And _
destSheet.Range("G" & j).Value = destSheet.Range("G" & i).Value Then
If destSheet.Range("G" & i).Value <> "" And destSheet.Range("G" & j).Value <> "" Then
If IsNumeric(destSheet.Range("G" & i).Value) And IsNumeric(destSheet.Range("G" & j).Value) Then
If destSheet.Range("G" & j).Value > destSheet.Range("G" & i).Value Then
destSheet.Range("A" & i & ":J" & i).Interior.Color = vbRed
destSheet.Range("A" & j & ":J" & j).Interior.Color = vbRed
Else
destSheet.Range("A" & i & ":J" & i).Interior.Color = vbYellow
destSheet.Range("A" & j & ":J" & j).Interior.Color = vbYellow
End If
End If
End If
End If
End If
Next j
Else
If destSheet.Range("C" & i).Value = "" Then
destSheet.Range("A" & i & ":J" & i).Interior.Color = xlNone
End If
End If
' Check for rows with matching columns C:F and higher quantity in G
If destSheet.Range("C" & i).Value <> "" And destSheet.Rows(i).Interior.Color <> vbYellow And destSheet.Rows(i).Interior.Color <> vbRed Then
For j = i + 1 To destLastRow
If destSheet.Range("C" & j).Value <> "" And destSheet.Rows(j).Interior.Color <> vbYellow And destSheet.Rows(j).Interior.Color <> vbRed Then
If destSheet.Range("C" & i).Value = destSheet.Range("C" & j).Value And _
destSheet.Range("D" & i).Value = destSheet.Range("D" & j).Value And _
destSheet.Range("E" & i).Value = destSheet.Range("E" & j).Value And _
destSheet.Range("F" & i).Value = destSheet.Range("F" & j).Value And _
IsNumeric(destSheet.Range("G" & i).Value) And IsNumeric(destSheet.Range("G" & j).Value) Then
If destSheet.Range("G" & j).Value > destSheet.Range("G" & i).Value Then
destSheet.Range("A" & i & ":J" & i).Interior.Color = vbRed
destSheet.Range("A" & j & ":J" & j).Interior.Color = vbRed
End If
End If
End If
Next j
End If
Next i
End Sub
Any suggestions or workaround would be appreciated.
Date | Del. Date | Order Number | Code | Description | Colour | Qty | CMT | Order Total | INV NO |
13/04/23 | 100001 | CON34 | CMT Conti Suit 34 | R/blue | 5 | R 30.00 | R 150.00 | ||
04/05/23 | 100001 | CON34 | CMT Conti Suit 34 | R/blue | 5 | R30.00 | R150.00 | 201 | |
13/04/23 | 100001 | CON36 | CMT Conti Suit 36 | R/blue | 5 | R 30.00 | R 150.00 | ||
04/05/23 | 100001 | CON36 | CMT Conti Suit 36 | R/blue | 10 | R30.00 | R300.00 | 201 | |
13/04/23 | 100001 | CON38 | CMT Conti Suit 38 | R/blue | 5 | R 30.00 | R 150.00 | ||
06/05/23 | 100001 | CON38 | CMT Conti Suit 38 | R/blue | 2 | R30.00 | R60.00 | 130 | |
04/05/23 | 100001 | CON38 | CMT Conti Suit 38 | R/blue | 3 | R30.00 | R90.00 | 201 | |
13/04/23 | 100001 | CON40 | CMT Conti Suit 40 | R/blue | 5 | R 30.00 | R 150.00 | ||
05/05/23 | 100001 | CON40 | CMT Conti Suit 40 | R/blue | 5 | R 30.00 | R 150.00 | 201 | |
05/05/23 | 100002 | CON48 | CMT Conti Suit 48 | Red | 5 | R 30.00 | R 150.00 | ||
05/05/23 | 100006 | CON40 | CMT Conti Suit 40 | Khaki | 10 | R 30.00 | R 300.00 |
as per this sample. I need a code to check each row (that is not highlighted- as this will take long with more data added) where the data matches from column C to column F, if the data match as in row 2 and row 3, then the quantity in column G must be checked against the quantity in the first row of the matching rows, if they match, then the rows from A:J must be highlighted in Yellow, if the quantity is less than the first matching row, then the rows must not be highlighted, if the quantity is more than the first matching row, then the rows must be highlighted in Red. Therefore in the above data, rows (2 and 3) and (9 and 10) must be highlighted in Yellow as they match the criteria. Rows 4 and 5 must be highlighted red as the quantity in row 5 is more than that in row 4. Then Rows 6, 7 and 8 in this sample must be highlighted in yellow, because rows 7 and 8 added is equal to the quantity in row 6, the same criteria must be applied even when there are more than 1 matching row where the data matches from column C to column F. I have somewhat managed to get to a point where the rows are highlighted Yellow and red but not when there are more than 2 rows when the quantities must be added and then matched to the first matching row, to either be highlighted Yellow, Red or not be highlighted.
Here is a sample of the code to where I managed to get it:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim destSheet As Worksheet
Dim destLastRow As Long
Dim i As Long
Dim j As Long
Dim orderNum As String
Set destSheet = ThisWorkbook.Worksheets("Jaburhisa Orders")
destLastRow = destSheet.Range("C" & destSheet.Rows.Count).End(xlUp).Row
For i = 2 To destLastRow
If Not destSheet.Rows(i).Interior.Color = vbYellow And Not destSheet.Rows(i).Interior.Color = vbRed And destSheet.Range("C" & i).Value <> "" Then
orderNum = destSheet.Range("C" & i).Value
For j = i + 1 To destLastRow
If Not destSheet.Rows(j).Interior.Color = vbYellow And Not destSheet.Rows(j).Interior.Color = vbRed And destSheet.Range("C" & j).Value <> "" Then
If destSheet.Range("C" & j).Value = orderNum And _
destSheet.Range("D" & j).Value = destSheet.Range("D" & i).Value And _
destSheet.Range("E" & j).Value = destSheet.Range("E" & i).Value And _
destSheet.Range("F" & j).Value = destSheet.Range("F" & i).Value And _
destSheet.Range("G" & j).Value = destSheet.Range("G" & i).Value Then
If destSheet.Range("G" & i).Value <> "" And destSheet.Range("G" & j).Value <> "" Then
If IsNumeric(destSheet.Range("G" & i).Value) And IsNumeric(destSheet.Range("G" & j).Value) Then
If destSheet.Range("G" & j).Value > destSheet.Range("G" & i).Value Then
destSheet.Range("A" & i & ":J" & i).Interior.Color = vbRed
destSheet.Range("A" & j & ":J" & j).Interior.Color = vbRed
Else
destSheet.Range("A" & i & ":J" & i).Interior.Color = vbYellow
destSheet.Range("A" & j & ":J" & j).Interior.Color = vbYellow
End If
End If
End If
End If
End If
Next j
Else
If destSheet.Range("C" & i).Value = "" Then
destSheet.Range("A" & i & ":J" & i).Interior.Color = xlNone
End If
End If
' Check for rows with matching columns C:F and higher quantity in G
If destSheet.Range("C" & i).Value <> "" And destSheet.Rows(i).Interior.Color <> vbYellow And destSheet.Rows(i).Interior.Color <> vbRed Then
For j = i + 1 To destLastRow
If destSheet.Range("C" & j).Value <> "" And destSheet.Rows(j).Interior.Color <> vbYellow And destSheet.Rows(j).Interior.Color <> vbRed Then
If destSheet.Range("C" & i).Value = destSheet.Range("C" & j).Value And _
destSheet.Range("D" & i).Value = destSheet.Range("D" & j).Value And _
destSheet.Range("E" & i).Value = destSheet.Range("E" & j).Value And _
destSheet.Range("F" & i).Value = destSheet.Range("F" & j).Value And _
IsNumeric(destSheet.Range("G" & i).Value) And IsNumeric(destSheet.Range("G" & j).Value) Then
If destSheet.Range("G" & j).Value > destSheet.Range("G" & i).Value Then
destSheet.Range("A" & i & ":J" & i).Interior.Color = vbRed
destSheet.Range("A" & j & ":J" & j).Interior.Color = vbRed
End If
End If
End If
Next j
End If
Next i
End Sub
Any suggestions or workaround would be appreciated.