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

Jignesh Thar

Do you mean to
1. Clear all data, comments,format
2. Apply Arail 10 font
in all 30 sheets except for the first row in each sheet?

Jignesh Thar

Try below
Sub ClearAndFormat()
For Each oWorksheet In Application.Sheets
oWorksheet.Activate
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
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

bsharath

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..

Jignesh Thar

Try below
Sub ClearAndFormat()
For Each oWorksheet In Application.Sheets
oWorksheet.Activate
Rows("2:65536").Select
Range(Selection, Selection.End(xlDown)).Select
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

Hitesh Manglani

'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

bsharath

ASKER

Both the macro's are working but not the Borders

Hitesh Manglani

'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(xlDiagonalDown).LineStyle = xlNone
oRange.Borders(xlDiagonalUp).LineStyle = xlNone
oRange.Borders(xlEdgeLeft).LineStyle = xlNone
oRange.Borders(xlEdgeTop).LineStyle = xlNone
oRange.Borders(xlEdgeBottom).LineStyle = xlNone
oRange.Borders(xlEdgeRight).LineStyle = xlNone
oRange.Borders(xlInsideVertical).LineStyle = xlNone
oRange.Borders(xlInsideHorizontal).LineStyle = 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

bsharath

ASKER

Hitesh Every thing is fine but not putting the Borders for the text which is there.

Hitesh Manglani

'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(xlDiagonalDown).LineStyle = xlContinuous
Selection.Borders(xlDiagonalUp).LineStyle = xlContinuous
Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
End If
Next

Next

End Sub

bsharath

ASKER

This is giving the borders with total X mark on the cells.

Hitesh Manglani

i did not understand you buddy, could you elaborate?

bsharath

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

Hitesh Manglani

'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(xlDiagonalDown).LineStyle = xlContinuous
Selection.Borders(xlDiagonalUp).LineStyle = xlContinuous
Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
End If
Next

Next

End Sub

bsharath

ASKER

Still the sam.

In the format cell > Borders the \ and this border is selected. /
Names has a X mark on each cell

ASKER CERTIFIED SOLUTION

Hitesh Manglani

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hitesh Manglani

Thanks for the grade Sharath :)

bsharath

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