Vba selection help

Question

I am after a VBA script which finds i.e. theJaye or number 100 within the spreadsheet (could be up to 60000 lines of data)and then selects all the cells that contain Jaye or 100 so that I can manually change the colours, font etc¡­ I have a script that will find and replace but as it is set to a specific colour and style it is very limited. I have tried modifying the script I have to select the cells as a range but it keeps selecting the whole range rather than just the specific cells.

I want the cells selected as a range so that I can manipulate the data. I could do it manually but theJaye or number 100 could appear thousands of times throughout the spreadsheet, therefore to manually click each cell would take a long time and normal find replace will not work as I run other macros also based on cell contents.

Any help would be appreciated.

Answer

Jaye

The following code will select all the cells in the used range with a value of 100 or Jaye.

Sub test()

Dim r As Range Dim combo As Range

Dim cl As Range

Set r = ActiveSheet.UsedRange

Set combo = Cells.Find(What:="Jaye", After:=ActiveCell, LookIn:=xlFormulas, LookAt _

       :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
       False, SearchFormat:=False)

For Each cl In r

   If Application.Or(cl.Value = 100, cl.Value = "Jaye") Then
       Set combo = Union(combo, cl)
   End If
   

Next cl

combo.Select

End Sub

You could possibly, and more simply, use conditional formatting to accomplish what you want.