Solved: highlighting matching cells

[Klartigue]

I have the below formula that highlights matching cells, but it also considers blank cells as mathing cells and highlights those as well and then labels those cash trades, which they arent.

[VBA]Sub Cashtrades()
Dim lr As Long, r As Long
Dim cell As Range
Dim str1 As String, str2 As String
ActiveWorkbook.Sheets(1).Activate
lr = Range("A65000").End(xlUp).Row
For Each cell In Range("A5:A" & lr)
r = cell.Row

If (StrComp(cell.Text, cell.Offset(0, 1).Text, vbTextCompare) = 0) Then

Range("A" & r & ":P" & r).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0

Set del = cell
del.Offset(0, 16).FormulaR1C1 = "CASH TRADE"


End With
End If
Next cell
End Sub[/VBA]

Is there a way to have it only highlight and label cash trades when there are values in the cells, not just blank cells?

[Bob Phillips]

Why not just test for blank

[vba]
If cell.Text <> "" Then

'other tests
End If[/vba]

[Bob Phillips]

If they are constants, you could also check thus

[vba]
For Each cell In Range("A5:A" & lr).SpecialCells(xlCellTypeConstants)[/vba]

[Klartigue]

See the attached document..this is a result of the below code and as you can see there are blank cells that are highlighted. My goal is to only have line 15 highlighted because the values in cells A15 and B15 match.

[VBA]Sub Cashtrades()
Dim lr As Long, r As Long
Dim cell As Range
Dim str1 As String, str2 As String
ActiveWorkbook.Sheets(1).Activate
lr = Range("A65000").End(xlUp).Row
For Each cell In Range("A5:A" & lr)
r = cell.Row
If (StrComp(cell.Text, cell.Offset(0, 1).Text, vbTextCompare) = 0) Then

Range("A" & r & ":P" & r).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0

Set del = cell
del.Offset(0, 16).FormulaR1C1 = "CASH TRADE"

End With
End If
Next cell
End Sub[/VBA]

[Bob Phillips]

Did you try either of my suggestions?

[Klartigue]

I tried the below but i get the same results as before:

[VBA]Sub Cashtrades()
Dim lr As Long, r As Long
Dim cell As Range
Dim str1 As String, str2 As String
ActiveWorkbook.Sheets(1).Activate
lr = Range("A65000").End(xlUp).Row
For Each cell In Range("A5:A" & lr).SpecialCells(xlCellTypeConstants)
r = cell.Row
If (StrComp(cell.Text, cell.Offset(0, 1).Text, vbTextCompare) = 0) Then

Range("A" & r & ":P" & r).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0

Set del = cell
del.Offset(0, 16).FormulaR1C1 = "CASH TRADE"

End With
End If
Next cell
End Sub[/VBA]

[Klartigue]

And then when i test for blank i keep getting the error end if without block if..

I think i am putting these codes in the wrong places?

[VBA]Sub Cashtrades()
Dim lr As Long, r As Long
Dim cell As Range
Dim str1 As String, str2 As String
ActiveWorkbook.Sheets(1).Activate
lr = Range("A65000").End(xlUp).Row
For Each cell In Range("A5:A" & lr).SpecialCells(xlCellTypeConstants)
r = cell.Row

If cell.Text <> "" Then


If (StrComp(cell.Text, cell.Offset(0, 1).Text, vbTextCompare) = 0) Then

Range("A" & r & ":P" & r).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End If

Set del = cell
del.Offset(0, 16).FormulaR1C1 = "CASH TRADE"

End With
End If
Next cell
End Sub[/VBA]

[Klartigue]

I figured it out.. see the below solution.

[VBA]Sub Cashtrades()
Dim lr As Long, r As Long
Dim cell As Range
Dim str1 As String, str2 As String
ActiveWorkbook.Sheets(1).Activate
lr = Range("A65000").End(xlUp).Row
For Each cell In Range("A5:A" & lr)
r = cell.Row

If cell.Text <> "" And (StrComp(cell.Text, cell.Offset(0, 1).Text, vbTextCompare) = 0) Then

Range("A" & r & ":P" & r).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0

Set del = cell
del.Offset(0, 16).FormulaR1C1 = "CASH TRADE"

End With
End If
Next cell
End Sub[/VBA]

Thanks for all your help!

[Bob Phillips]

And then when i test for blank i keep getting the error end if without block if..

I think i am putting these codes in the wrong places?

Just look at your construct pairings - should be self-evident.

You have

[vba]
If ....

With ...

...
End If

...
End With[/vba]