aayaanmayank
Board Regular
- Joined
- Jul 20, 2018
- Messages
- 157
Hi, Can any one help me in this, i have a column contains ID and another column contains company names.
i have to first check that how many cells or times one/same id contains and then match and color code (if matches then green else yellow) company name with the similar times.
Below is the data-
[TABLE="width: 100, align: left"]
<tbody>[TR]
[TD]AE36630GU[/TD]
[TD][TABLE="width: 204"]
<tbody>[TR]
[TD="class: xl65"]PHARMACEUTICAL INDUSTRIES[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]AE36630GU[/TD]
[TD][TABLE="width: 204"]
<tbody>[TR]
[TD="class: xl65"]PHARMACEUTICAL INDUSTRIES[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65"]ALJ61924662T[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 204"]
<tbody>[TR]
[TD="class: xl65"]ABC INVEST[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65"]ALJ61924662T[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 204"]
<tbody>[TR]
[TD="class: xl65"][TABLE="width: 204"]
<tbody>[TR]
[TD="class: xl65"]ABC INVEST SH.P.K (L.T.D.)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65"]ALJ61924662T[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 204"]
<tbody>[TR]
[TD="class: xl65"]ABC INVEST SHPK[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65"]ALK43128401L[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 204"]
<tbody>[TR]
[TD="class: xl65"]INVEST PETROLEUM ALBANIA LTD[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65"]ALK43128401L[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 204"]
<tbody>[TR]
[TD="class: xl65"]INVEST PETROLEUM ALBANIA LTD[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65"]ALK82418662C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 204"]
<tbody>[TR]
[TD="class: xl65"]RAH HYDROPOWER[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65"]ALK82418662C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 204"]
<tbody>[TR]
[TD="class: xl65"]RAH HYDROPOWER SH.A.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65"]ALK82418662C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 204"]
<tbody>[TR]
[TD="class: xl65"]RAH Hydropower Sh.A.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 293"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
so far i have prepared a macro which is moving row by row but that only matches company names with previous row
Set SHGROUP = ThisWorkbook.Worksheets("Data_DQ")
Range("F2").Select
ActiveCell.Interior.Color = vbYellow
For U = 2 To LastRow1
Set MYNAME2 = Cells(U, "f")
Set MYNAME3 = Cells((U + 1), ("f"))
If MYNAME2.Value = MYNAME3 Then
SHGROUP.Cells(U, "f").Interior.Color = vbGreen
SHGROUP.Cells(U + 1, "f").Interior.Color = vbGreen
Else
SHGROUP.Cells(U + 1, "f").Interior.Color = vbYellow
End If
Next U
Last edited: