Change the Text and Background color in excel sheet

[K Raghavender rao]

Hi All,

I need to help from you guys, i got 1000 excel sheets in folder..i need to open each and every excel workbook (each workbook has only one sheet) and change the background color to white and text as black.

Kindly help me in providing the VBA code which can do this job for me easily.

Thanks,
K.Raghavender rao

 

[rumshar]

Hi Raghavender,
Please see if the attached helps.
Pls ignore the button name, since I modified this macro I forgot to rename it.

With Regards
Rudra

 

[Luke M]

This should do it. Note that this won't override conditional formatting, if that is present. If you have that many files, this may take awhile to run...

Option Explicit

Sub OpenBooks()
Dim myPath As String
Dim bookName As String
Dim opBook As Workbook
Dim ws As Worksheet
Dim i As Long


'Where is the folder?
myPath = "C:\My Documents\"

If Right(myPath, 1) <> "\" Then
    myPath = myPath & "\"
End If

'Find the name of a workbook
bookName = Dir(myPath & "*.xls*")
i = 0

Application.ScreenUpdating = False
Do Until bookName = ""
    'Open the workbooks
    Set opBook = Workbooks.Open(myPath & bookName, False)
    'Loop through the worksheets
    For Each ws In opBook.Worksheets
        With ws.Cells
            'Change the colors
            .Interior.ColorIndex = 0
            .Font.ColorIndex = 1
        End With
    Next ws
    'Close and save
    Application.DisplayAlerts = False
    opBook.Close savechanges:=True
    Application.DisplayAlerts = True
    i = i + 1
    Set opBook = Nothing
    'Get next workbook name
    bookName = Dir()
Loop
Application.ScreenUpdating = False

MsgBox "Files updated: " & i, vbOKOnly, "Finished"
End Sub

 

[Hui]

The original post said there was only 1 Worksheet in each Workbook

If that is true

Replace all 6 lines:

For Each ws In opBook.Worksheets
        With ws.Cells
            .Interior.ColorIndex = 0
            .Font.ColorIndex = 1
        End With
Next ws

With these two lines:

            Cells.Interior.ColorIndex = 0
            Cells.Font.ColorIndex = 1

Which will speed it up a little

 

[K Raghavender rao]

Thanks to all