Need help with fixing code - Match FName + LName and highlightcells

[Denblanc]

Need help to fix my code. Problem: Compare FirstName (Col C) to LastName (Col D) if matches specific case highlight text in both cells and move on. Spreadsheet row length changes day-by-day and several name matches to find in several thousand rows.




Sub TransitStrike()

Dim Rng1 As Range
On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range("C1:E1")
Else
Set Rng1 = Union(Range("C1:E1"), Rng1)
End If
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Font.Color = False
Case "John", "Smith"
Cell.Font.Color = 9
Case "Eric", "Jones"
Cell.Font.Color = 9
Case "Mike", "Bloom"
Cell.Font.Color = 9
End Select
Next

End Sub

[SamT]

Sub TransitStrike()

Dim Rng1 As Range
Dim Cel As Range

Set Rng1 = Range(("C1"), Range("D1").End(xldown))
If Rng.Rows.Count = Rows.Count then Exit Sub

For Each Cel In Rng1.Columns(1)
Select Case Cel & Cel.Offset(,1)
Case vbNullString
Cell.Font.ColorIndex = xlColorIndexNone 'Can also use xlColorIndexAutomatic
Case "JohnSmith"
Cell.Font.ColorIndex = 9
Case "EricJones"
Cell.Font.ColorIndex = 9
Case "MikeBloom"
Cell.Font.ColorIndex= 9
End Select
Next

End Sub

[Denblanc]

I copied your solution and am getting an "error 13" mismatch message on the following line:



Select Case Cel & Cel.Offset(,1)


I tried substituting a '0' in front of the comma & 1; also tried changing the first Cel to ActiveCel.

[SamT]

What is actually in those cells? If formulas, what are they displaying?

[Denblanc]

Column C - First names (John, Michael, Eric, etc...) Column D - Last names

There are no blanks or numbers in column cells - both columns are required fields; which have no formulas associated in either column. Neither column of names are being highlighted.

[skywriter]

Perhaps add .cells to the end of the for each line.

For Each Cel In Rng1.Columns(1).Cells

[Denblanc]

Well, I am getting no error messages but nothing is highlighted. Thanks for trying to help. Do you think if I combine the two columns into one then try to highlight would be easier?

[SamT]

First, edit the Rng1 assignment to as below. It had an error and you don't need to cycle thru the D column, the Offset takes care of that. :devil2:

If that doesn't fix it, then continue as indicated below.

Paste this sub in, then click anywhere in the sub.

Press F8 repeatedly to Step thru the sub. Hover the mouse over the X. When the line after the X = Rng1.Address in executed. a ToolTip will show the value of X, for X after any of the previous lines are executed. (All X'es will show Rng1.Address, Cel.Address, and Cel.Value.)


Sub TransitStrike()

Dim Rng1 As Range
Dim Cel As Range

Dim X

Set Rng1 = Range(Range("C1"), Range("C1").End(xldown))
If Rng.Rows.Count = Rows.Count Then Exit Sub

X = Rng1.Address

For Each Cel In Rng1.Columns(1)

X = Cel.Address
X = Cel.Value

Select Case Cel.Value & Cel.Offset(,1).Value
Case vbNullString
Cell.Font.ColorIndex = xlColorIndexNone 'Can also use xlColorIndexAutomatic
Case "JohnSmith"
Cell.Font.ColorIndex = 9
Case "EricJones"
Cell.Font.ColorIndex = 9
Case "MikeBloom"
Cell.Font.ColorIndex= 9
End Select
Next

End Sub

[Denblanc]

When tapping F8 & hovering over "X" it shows C1:C1490 (nothing else - correct amount of rows in column)
Until the line (Select Case Cel.Value & Cel.Offset(, 1).Value) - shows error 13.

[Denblanc]

End result was to combine Columns C & D into one column and use an array (with everyone's name) to search single column and highlight when a match was found.

Thanks for your help with the situation.