PDA

View Full Version : Solved: highlighting matching cells



Klartigue
04-20-2012, 11:43 AM
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.

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

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
04-20-2012, 11:50 AM
Why not just test for blank


If cell.Text <> "" Then

'other tests
End If

Bob Phillips
04-20-2012, 11:53 AM
If they are constants, you could also check thus


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

Klartigue
04-20-2012, 11:58 AM
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.

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

Bob Phillips
04-20-2012, 01:00 PM
Did you try either of my suggestions?

Klartigue
04-20-2012, 01:05 PM
I tried the below but i get the same results as before:

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

Klartigue
04-20-2012, 01:07 PM
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?

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

Klartigue
04-20-2012, 02:21 PM
I figured it out.. see the below solution.

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

Thanks for all your help!

Bob Phillips
04-21-2012, 02:45 AM
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


If ....

With ...

...
End If

...
End With