Darkveemon1
New Member
- Joined
- Jul 12, 2023
- Messages
- 11
- Office Version
- 365
- Platform
- Windows
Hi, I'm making a simple VBA SubI, but it is giving me some problems.
I have a column with some values and I want to higlight the occurencies (a value is repeated), but the first tiem the value appears it should not be higlighted.
Fro Example:
if I have a column like this:
d
d
d
a
e
f
a
w
it should color the background of cell D6, D7 and D11 (my first cell is D5)
The problem with the code is that it works the first iteration, but then changes the value D6 to the value it is checking in the For loop, so in the end D6 is blue, but with "w" in it instead of "d" and D7 and D11 are still blank
I have a column with some values and I want to higlight the occurencies (a value is repeated), but the first tiem the value appears it should not be higlighted.
Fro Example:
if I have a column like this:
d
d
d
a
e
f
a
w
it should color the background of cell D6, D7 and D11 (my first cell is D5)
The problem with the code is that it works the first iteration, but then changes the value D6 to the value it is checking in the For loop, so in the end D6 is blue, but with "w" in it instead of "d" and D7 and D11 are still blank
VBA Code:
Sub Pulsante1_Click()
Dim wb As Workbook
Dim tld As Worksheet
Dim supportoTLD As Range 'my column
Dim ricerca, confronta As Range 'ricerca is the cell I will be confronting every time, 'confronta is the cell the cell after it and so on in the second loop
Dim i, j As Integer 'row index
Set wb = ThisWorkbook
Set tld = wb.Sheets("Foglio1")
Set supportoTLD = tld.Columns("D:D")
Set ricerca = tld.Cells(5, 4)
Set confronta = tld.Cells(6, 4)
For i = 5 To Range("D5").End(xlDown).Row - 1
For j = 6 To Range("D5").End(xlDown).Row
ricerca = tld.Cells(i, supportoTLD.Column).Value
confronta = tld.Cells(j, supportoTLD.Column).Value
If confronta = ricerca Then
confronta.Interior.ColorIndex = 37
End If
Next j
Next i
End Sub