highlight if the same combination of data appears in another row-CodePudding

enter image description hereHi does anyone know how to use excel vba to highlight if the same combination of data appears in another row, within the same group of items (an empty row is used to split them)? example

CodePudding user response：

You can use conditional formatting with a "helper column"

Formula for helper column:
column can be anyplace on the worksheet, and can be hidden

D2: =A2&B2&C2   *and fill down as far as needed*

Then select the three column/ranges to be formatted. Conditional formatting using a formula:

=AND(COUNTIF($D$2:$D$15,$D2)>1,$D2<>"")

and set the format for your interior fill

CodePudding user response：

Try to this code it my help!

Sub InsBl()
Dim rng, cel As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
Set rng = Range("A2:A" & LR)
For Each cel In rng
If WorksheetFunction.CountIf(rng, cel.Value) > 1 Then
cel.Interior.ColorIndex = 6
Else
cel.Interior.ColorIndex = xlNone
End If
Next cel
End Sub

Dim rng, rng1, cel, cel1 As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
Set rng = Range("B2:B" & LR)
Set rng1 = Range("C2:C" & LR)
For Each cel In rng
If WorksheetFunction.CountIf(rng, cel.Value) > 1 Then
For Each cel1 In rng1
If WorksheetFunction.CountIf(rng1, cel.Offset(0, 1).Value) > 1 Then
cel.Offset(0, 1).Interior.ColorIndex = 6
cel.Interior.ColorIndex = 6
End If
Next cel1
Else
cel.Interior.ColorIndex = xlNone
End If
Next cel