write at exceltoexplore@gmail.com : Report Automation|Dashboard in Excel| Provide Excel consulting through macro (VBA) automation |Financial Modeling | Ethical Hacking

Tuesday, 7 May 2013

Excel : Find Sring in all worksheets Highlight cell and Zoom the excel worksheet

This requirement came from forum & most appreciated thing in this is to Zoom the excel worksheet. once the search string is found.  Zoom is used because that person has eyesight problem so he can view the result clearly.
  1. Enter Search string/Value in input box
  2. it will search for string/vlaue in all the worksheets of workbook
  3. it will give you cell address of the search string/value if found
  4. Excel worksheet will be zoom Out and Zoom in again
  5. Found result will be highlighted
  6. it will give you pop up , whether you want to clear highlighting ?
Sub Mtest()
Dim found As Range
Dim m As String, Temp As String
Dim count As Integer
Dim ws As Worksheet
count = 0
m = InputBox(prompt:="Enter value for search", Title:="Excel Find")
For Each ws In ActiveWorkbook.Worksheets
Set found = ws.Cells.Find(What:=m, LookIn:=xlValues, lookat:=xlPart)
If Not found Is Nothing Then
count = count + 1
MsgBox found.Worksheet.Name & found.Cells.Address, Title:="Excel Find"
'found.Cells.Interior.ColorIndex = 6
With found.Cells.Borders
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With
ActiveWindow.Zoom = ActiveWindow.Zoom + 50
Application.Wait Now + TimeValue("00:00:02")
ActiveWindow.Zoom = ActiveWindow.Zoom - 50
Temp = MsgBox(prompt:="Clear highlighting", Title:="Excel Find", Buttons:=vbOKCancel + vbQuestion)
'If Temp = vbOK Then found.Cells.Interior.ColorIndex = xlNone
If Temp = vbOK Then found.Borders.LineStyle = xlNone
End If
Next ws
If count = 0 Then MsgBox prompt:="Not found", Title:="Excel Find"
End Sub

No comments:

Post a Comment