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.
The following code will select all the cells in the used range with a value of 100 or Jaye.
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
You could possibly, and more simply, use conditional formatting to accomplish what you want.