I have been trying to make a code that would run on all sheets a folder contains. But i am stuck that how to do this. I have wrote some line and gather information via google but difficult to create this one.
Here is the code which is required to run on all workbooks with sheet name i.e. DATA
Code
Dim ws As Worksheet
Dim mySel As Range, aCell As Range
Set ws = ThisWorkbook.Sheets("DATA")
Set mySel = ws.UsedRange
For Each aCell In mySel
With aCell
.Interior.Color = .DisplayFormat.Interior.Color
End With
Next aCell
mySel.FormatConditions.Delete
Display More
Here is the second one which provide the option to select the folder.
Code
Sub Folder()
Dim strFolder As String
Dim strFile As String
Dim wbk As Workbook
Dim wsh As Worksheet
Dim I As Long
Dim xRg As Range
With Application.FileDialog(4)
If .Show Then
strFolder = .SelectedItems(1)
Else
MsgBox "You haven't selected a folder!", vbExclamation
Exit Sub
End If
End With
Application.ScreenUpdating = False
If Right(strFolder, 1) <> "\" Then
strFolder = strFolder & "\"
End If
End Sub
strFile = Dir(strFolder & "*.xls*")
Do While strFile <> ""
Set wbk = Workbooks.Open(strFolder & strFile)
For I = 2 To wbk.Worksheets.Count
Display More
Your help will be highly appreciated.