Iterate over all cells in all sheets in a workbook, change the format if red

Question

I am trying to change all red text to black in all cells for all sheets of an Excel workbook. I am using the following:

Sub RedToBlack() Dim Current As Worksheet For Each Current In Worksheets For Each cell In Current If cell.Font.ColorIndex = 3 Then cell.Font.ColorIndex = 0 End If Next Next End Sub 

I know this is wrong, but I wanted to make it clear what I'm trying to do. Can anyone offer advice? Thanks for your help, I am very new to this.

Answer 1

What you have is likely to do the job. However, this would be extremely inefficient. The best way would be to find the find and replace it as follows.

 'set the next find to find the red With Application.FindFormat.Font .ColorIndex = 3 End With 'set the next find to replace with black With Application.ReplaceFormat.Font .ColorIndex = 1 End With 'Do the actual replacing For Each aSheet In ActiveWorkbook.Worksheets aSheet.Activate Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True Next aSheet 

This is done for all cells of all sheets for the entire book. You can also do this by going to the regular search and replace window, then clicking the options button.

Answer 2

 Sub change_color() For Each sht In ActiveWorkbook.Sheets Set rng = sht.UsedRange For Each cell In rng If cell.Font.ColorIndex = 3 Then cell.Font.ColorIndex = 0 End If Next cell Next sht End Sub