bsharath
asked on
Excel Macro to do all these things
Hi,
I have 30 sheets in a excel.Is there a way to do all these things
1. Remove all comments
2. Size of font to 10 and Arial
3. All text borders
4. Remove all Colors for cells if any
5. Make all text slank
6. Remove any Bold found
Exclude all these for the fist row as they are fields
regards
Sharath
I have 30 sheets in a excel.Is there a way to do all these things
1. Remove all comments
2. Size of font to 10 and Arial
3. All text borders
4. Remove all Colors for cells if any
5. Make all text slank
6. Remove any Bold found
Exclude all these for the fist row as they are fields
regards
Sharath
Try below
Sub ClearAndFormat()
For Each oWorksheet In Application.Sheets
oWorksheet.Activate
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Sel ect
Selection.Clear
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Next
End Sub
Sub ClearAndFormat()
For Each oWorksheet In Application.Sheets
oWorksheet.Activate
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Sel
Selection.Clear
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Next
End Sub
ASKER
Clear all Comments
Apply Arial,10
Remove all colors in cell and font color to black
Remove all bold data.
None of the above has to be done for the fist line header..
Apply Arial,10
Remove all colors in cell and font color to black
Remove all bold data.
None of the above has to be done for the fist line header..
Try below
Sub ClearAndFormat()
For Each oWorksheet In Application.Sheets
oWorksheet.Activate
Rows("2:65536").Select
Range(Selection, Selection.End(xlDown)).Sel ect
Selection.ClearFormats
Selection.ClearComments
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Next
End Sub
Sub ClearAndFormat()
For Each oWorksheet In Application.Sheets
oWorksheet.Activate
Rows("2:65536").Select
Range(Selection, Selection.End(xlDown)).Sel
Selection.ClearFormats
Selection.ClearComments
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Next
End Sub
'here is a modification of the above macro that should work
Sub ClearAndFormat()
Dim oRange as Range
For Each oWorksheet In Application.Sheets
Set oRange= oWorksheet.UsedRange
oRange.ClearFormats
oRange.ClearComments
With oRange.Font
.Name = "Arial"
.Size = 10
.Bold = False
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Bold = False 'Add this
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Next
End Sub
Sub ClearAndFormat()
Dim oRange as Range
For Each oWorksheet In Application.Sheets
Set oRange= oWorksheet.UsedRange
oRange.ClearFormats
oRange.ClearComments
With oRange.Font
.Name = "Arial"
.Size = 10
.Bold = False
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Bold = False 'Add this
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Next
End Sub
ASKER
Both the macro's are working but not the Borders
'here is a modification of the above macro that should work
Sub ClearAndFormat()
Dim oRange as Range
For Each oWorksheet In Application.Sheets
Set oRange= oWorksheet.UsedRange
oRange.ClearFormats
oRange.ClearComments
oRange.Borders(xlDiagonalD own).LineS tyle = xlNone
oRange.Borders(xlDiagonalU p).LineSty le = xlNone
oRange.Borders(xlEdgeLeft) .LineStyle = xlNone
oRange.Borders(xlEdgeTop). LineStyle = xlNone
oRange.Borders(xlEdgeBotto m).LineSty le = xlNone
oRange.Borders(xlEdgeRight ).LineStyl e = xlNone
oRange.Borders(xlInsideVer tical).Lin eStyle = xlNone
oRange.Borders(xlInsideHor izontal).L ineStyle = xlNone
With oRange.Font
.Name = "Arial"
.Size = 10
.Bold = False
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Bold = False 'Add this
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Next
End Sub
Sub ClearAndFormat()
Dim oRange as Range
For Each oWorksheet In Application.Sheets
Set oRange= oWorksheet.UsedRange
oRange.ClearFormats
oRange.ClearComments
oRange.Borders(xlDiagonalD
oRange.Borders(xlDiagonalU
oRange.Borders(xlEdgeLeft)
oRange.Borders(xlEdgeTop).
oRange.Borders(xlEdgeBotto
oRange.Borders(xlEdgeRight
oRange.Borders(xlInsideVer
oRange.Borders(xlInsideHor
With oRange.Font
.Name = "Arial"
.Size = 10
.Bold = False
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Bold = False 'Add this
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Next
End Sub
ASKER
Hitesh Every thing is fine but not putting the Borders for the text which is there.
'ok try this
Sub ClearAndFormat()
Dim oRange As Range
For Each oWorksheet In Application.Sheets
Set oRange = oWorksheet.UsedRange
oRange.ClearFormats
oRange.ClearComments
With oRange.Font
.Name = "Arial"
.Size = 10
.Bold = False
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Bold = False 'Add this
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
For Each cell In oRange
If cell.Value <> "" Then
Range(cell.Address).Select
Selection.Borders(xlDiagon alDown).Li neStyle = xlContinuous
Selection.Borders(xlDiagon alUp).Line Style = xlContinuous
Selection.Borders(xlEdgeLe ft).LineSt yle = xlContinuous
Selection.Borders(xlEdgeTo p).LineSty le = xlContinuous
Selection.Borders(xlEdgeBo ttom).Line Style = xlContinuous
Selection.Borders(xlEdgeRi ght).LineS tyle = xlContinuous
End If
Next
Next
End Sub
Sub ClearAndFormat()
Dim oRange As Range
For Each oWorksheet In Application.Sheets
Set oRange = oWorksheet.UsedRange
oRange.ClearFormats
oRange.ClearComments
With oRange.Font
.Name = "Arial"
.Size = 10
.Bold = False
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Bold = False 'Add this
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
For Each cell In oRange
If cell.Value <> "" Then
Range(cell.Address).Select
Selection.Borders(xlDiagon
Selection.Borders(xlDiagon
Selection.Borders(xlEdgeLe
Selection.Borders(xlEdgeTo
Selection.Borders(xlEdgeBo
Selection.Borders(xlEdgeRi
End If
Next
Next
End Sub
ASKER
This is giving the borders with total X mark on the cells.
i did not understand you buddy, could you elaborate?
ASKER
What ever text is there it should give borders to it.This is what i want.
But what happens is it Gives the borders and even Strike the cells as a X mark
But what happens is it Gives the borders and even Strike the cells as a X mark
'ok try this buudy and let me know if it works for you
Sub ClearAndFormat()
Dim oRange As Range
For Each oWorksheet In Application.Sheets
Set oRange = oWorksheet.UsedRange
oRange.ClearFormats
oRange.ClearComments
With oRange.Font
.Name = "Arial"
.Size = 10
.Bold = False
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Bold = False 'Add this
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
For Each cell In oRange
If cell.Value <> "" and cell.value <> "X" Then
Range(cell.Address).Select
Selection.Borders(xlDiagon alDown).Li neStyle = xlContinuous
Selection.Borders(xlDiagon alUp).Line Style = xlContinuous
Selection.Borders(xlEdgeLe ft).LineSt yle = xlContinuous
Selection.Borders(xlEdgeTo p).LineSty le = xlContinuous
Selection.Borders(xlEdgeBo ttom).Line Style = xlContinuous
Selection.Borders(xlEdgeRi ght).LineS tyle = xlContinuous
End If
Next
Next
End Sub
Sub ClearAndFormat()
Dim oRange As Range
For Each oWorksheet In Application.Sheets
Set oRange = oWorksheet.UsedRange
oRange.ClearFormats
oRange.ClearComments
With oRange.Font
.Name = "Arial"
.Size = 10
.Bold = False
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Bold = False 'Add this
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
For Each cell In oRange
If cell.Value <> "" and cell.value <> "X" Then
Range(cell.Address).Select
Selection.Borders(xlDiagon
Selection.Borders(xlDiagon
Selection.Borders(xlEdgeLe
Selection.Borders(xlEdgeTo
Selection.Borders(xlEdgeBo
Selection.Borders(xlEdgeRi
End If
Next
Next
End Sub
ASKER
Still the sam.
In the format cell > Borders the \ and this border is selected. /
Names has a X mark on each cell
In the format cell > Borders the \ and this border is selected. /
Names has a X mark on each cell
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for the grade Sharath :)
ASKER
Hitesh
Thanks a lot
Any help with these Q....
https://www.experts-exchange.com/questions/22767433/Excel-macro-to-find-for-text-and-mark-the-whole-row-colored.html
https://www.experts-exchange.com/questions/22766614/Excel-sheet-Shift's-for-my-team-Challenging.html
https://www.experts-exchange.com/questions/22756388/Sort-excel-sheet-as-per-the-text-file.html
https://www.experts-exchange.com/questions/22731145/Need-to-sort-the-excel-as-per-my-txt-file.html
Thanks a lot
Any help with these Q....
https://www.experts-exchange.com/questions/22767433/Excel-macro-to-find-for-text-and-mark-the-whole-row-colored.html
https://www.experts-exchange.com/questions/22766614/Excel-sheet-Shift's-for-my-team-Challenging.html
https://www.experts-exchange.com/questions/22756388/Sort-excel-sheet-as-per-the-text-file.html
https://www.experts-exchange.com/questions/22731145/Need-to-sort-the-excel-as-per-my-txt-file.html
1. Clear all data, comments,format
2. Apply Arail 10 font
in all 30 sheets except for the first row in each sheet?