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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.