Solved Channging Font Color of entire workbook

Microsoft Office excel 2007 home & stude...
August 31, 2011 at 16:33:41
Specs: Windows XP
Can someone help me create a macro or tell me how to accomplish the following? I would like the macro to search the entire Microsoft Excel workbook for a particular font color. I have an extremely large amount of data and mutltiple worksheets (over 20). I have some font that are red and some font that are orange. I would like a macro that I can run to search for all "red" and "orange" font color (throughout the entire Excel wrokbook, all worksheets) and change the font color to black. The reasoning behind this is that I need to constantly change font color to track some issues, but at the end of the month, I have to revert the font color back to black. However, there are some font color throughout the workbook that doesn't need to be changed, such as headers and titles. So I can't just simply select the entire sheet and change all the font color to black. Plus I would have to do this 20+ times. I figure if I can run a macro, this would save me a lot of time and hassle every month.

Thanks in Advance

See More: Channging Font Color of entire workbook

Report •

August 31, 2011 at 21:09:44
✔ Best Answer
Assuming that you are using the basic Red and Orange and not some custom color, this should work. I suggest that you try it in a backup copy of your workbook since it can't easily be undone.

Note: There appears to be a different ColorIndex number for Orange between Excel 2003 and Excel 2010.

If you are running Excel 2003, use ColorIndex 46 for Orange. For 2010 (and I assume 2007) try 44.

Depending on how big your spreadsheets are this could take a while since it checks every cell in the UsedRange.

Sub ResetRedOrange()
'Loop through Sheets
  For shtNum = 1 To Sheets.Count
'Determine range of cells used
    rngAddress = Sheets(shtNum).UsedRange.Address
'Loop through UsedRange
      For Each cel In Sheets(shtNum).Range(rngAddress)
'Check for font color of Red (3) or Orange (44 or 46)
'Change it to Automatic if found
        If cel.Font.ColorIndex = 3 Or _
           cel.Font.ColorIndex = 46 Then
               cel.Font.ColorIndex = xlAutomatic
        End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

September 1, 2011 at 18:36:28
Thanks again DerbyDad03! You are totally awesome!

Report •

Related Solutions

Ask Question