Hello, this is what i have so far. But if either of the search values from MyArr (A1 or A2) are left blank then it will search for a blank cell. How do I prevent this? Help much appreciated.
Option Explicit
Sub Mark_Cells_In_Column()
Dim FirstAddress As String, I As Long
Dim MyArr As Variant, Rng As Range, Sh As Worksheet
Sheets("Sheet1").Select
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
MyArr = Array(Range("A1").Value, Range("A2").Value)
For Each Sh In ThisWorkbook.Worksheets
If Sh.Name <> "Sheet1" Then
With Sh.Range("A:A")
.Offset(0, 1).ClearContents
For I = LBound(MyArr) To UBound(MyArr)
Set Rng = .Find(What:=MyArr(I), After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not Rng Is Nothing Then
FirstAddress = Rng.Address
Do
Rng.Offset(0, 1).Value = Range("B1").Value 'B1<=|'
Rng.Offset(0, 2).Value = Range("C1").Value 'C1<=|'
Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
End If
Next I
End With
End If
Next Sh
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Bookmarks