I am repeating the following code many times for varying cell contents. I know there must be a better way. I tried to change the code to array myself but apparently I was not doing something correctly. I am new to this (duh.lol)! I found the code in a forum and changed highlight cell to highlight entire row because the next step is to copy all rows in which are highlighted to a new sheet within the same workbook. Also the cell contents the code is searching for can be anywhere in column A thru M on any given row on the active sheet. I have Excel 2013 and am running Windows8.1.
1. Should I use variant array, string array, named range(no clue how to do but have read about it), or another function?
2.Could you please point me in the right direction as to how to copy highlighted rows to a new sheet?
3.Lastly, should I be highlighting the entire row or just the active cells(as that is all I need) in the needed row? .
Your help is very appreciated!!!
Sub HighlightCells()
Dim i As Long
Dim Fnd As String
Dim fCell As Range
Fnd = ("DVOK8467*")
Set fCell = Range("A1")
For i = 1 To WorksheetFunction.CountIf(ActiveSheet.UsedRange, Fnd)
Set fCell = Cells.Find(What:=Fnd, After:=fCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If fCell Is Nothing Then
MsgBox Fnd & " not on sheet !!"
Exit Sub
Else
With fCell
.EntireRow.Interior.ColorIndex = 6
End With
End If
Next i
Fnd = ("DVOK8443*")
Set fCell = Range("A1")
For i = 1 To WorksheetFunction.CountIf(ActiveSheet.UsedRange, Fnd)
Set fCell = Cells.Find(What:=Fnd, After:=fCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If fCell Is Nothing Then
MsgBox Fnd & " not on sheet !!"
Exit Sub
Else
With fCell
.EntireRow.Interior.ColorIndex = 6
End With
End If
Next i
Fnd = ("DVOK8720*")
Set fCell = Range("A1")
For i = 1 To WorksheetFunction.CountIf(ActiveSheet.UsedRange, Fnd)
Set fCell = Cells.Find(What:=Fnd, After:=fCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If fCell Is Nothing Then
MsgBox Fnd & " not on sheet !!"
Exit Sub
Else
With fCell
.EntireRow.Interior.ColorIndex = 6
End With
End If
Next i
Fnd = ("5DVIA8797*")
Set fCell = Range("A1")
For i = 1 To WorksheetFunction.CountIf(ActiveSheet.UsedRange, Fnd)
Set fCell = Cells.Find(What:=Fnd, After:=fCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If fCell Is Nothing Then
MsgBox Fnd & " not on sheet !!"
Exit Sub
Else
With fCell
.EntireRow.Interior.ColorIndex = 6
End With
End If
Next i
Fnd = ("DVIA8809*")
Set fCell = Range("A1")
For i = 1 To WorksheetFunction.CountIf(ActiveSheet.UsedRange, Fnd)
Set fCell = Cells.Find(What:=Fnd, After:=fCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If fCell Is Nothing Then
MsgBox Fnd & " not on sheet !!"
Exit Sub
Else
With fCell
.EntireRow.Interior.ColorIndex = 6
End With
End If
Next i
End Sub
Again thank you for taking the time to read my ramblings and helping me!
Bookmarks