VBA code for highlighting rows that match Yellow or Red

Kassie1

New Member
Joined
Aug 19, 2015
Messages
5
I have a sheet where data is posted from other sheets from column A to column J starting from row 2,
DateDel. DateOrder NumberCodeDescriptionColourQtyCMTOrder TotalINV NO
13/04/23​
100001CON34CMT Conti Suit 34R/blue5
R 30.00​
R 150.00​
04/05/23​
100001​
CON34CMT Conti Suit 34R/blue
5​
R30.00​
R150.00​
201
13/04/23​
100001CON36CMT Conti Suit 36R/blue5
R 30.00​
R 150.00​
04/05/23​
100001​
CON36CMT Conti Suit 36R/blue
10​
R30.00​
R300.00​
201
13/04/23​
100001CON38CMT Conti Suit 38R/blue5
R 30.00​
R 150.00​
06/05/23​
100001CON38CMT Conti Suit 38R/blue
2​
R30.00​
R60.00​
130​
04/05/23​
100001​
CON38CMT Conti Suit 38R/blue
3​
R30.00​
R90.00​
201
13/04/23​
100001CON40CMT Conti Suit 40R/blue5
R 30.00​
R 150.00​
05/05/23​
100001​
CON40CMT Conti Suit 40R/blue
5​
R 30.00​
R 150.00​
201
05/05/23​
100002CON48CMT Conti Suit 48Red5
R 30.00​
R 150.00​
05/05/23​
100006CON40CMT Conti Suit 40Khaki10
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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top