Font Color Changes

[PivotMeThis]

I have a second post about copy/paste on the board that deals with the same worksheet but this is a different issue. The data that I am working with are made up of projects and contracts. A contract can have several projects and the data is sorted so that the projects are together on several rows that make up the contract. On the first row of each group of projects are formulas to add up the project costs for each contract and well as project status information that is updated weekly by several people.

When this data is updated I have a little code that turns the font blue. I thought this was pretty slick (and it is, to a point) and that point is, it will always be blue.

Private Sub Worksheet_Change(ByVal Target As Range)
    Target.Font.ColorIndex = 5
End Sub

The problem is, that after this group of people make their updates, there is another person who will review these updates and make possible corrections. When she is done, I'd like the font to return to black - with the help of a button or checkbox that she can select. A checkbox for each contract would be nice but there is project information between some of these lines. I have placed an "X" in column A for each contract line if that is helpful. If that idea is not an option, just one button for the entire sheet, so when she finishes editing it returns to blue.

This report is updated weekly. It's something that will reside on our network with several people working on it.

 

[PivotMeThis]

This worksheet is protected with only the fields they can edit available to them. I am getting an error when I have protection on. I am working on a solution now but thought I should mention it.

 

[PivotMeThis]

I changed the code to this, so I don't get errors with the sheet protected. Still looking for a way to turn it back to black.

Private Sub Worksheet_Change(ByVal Target As Range)
    With Sheets("ProjectData")
    .Unprotect
    Target.Font.ColorIndex = 5
    .Protect
    End With
End Sub

 

[shg]

Target.Font.ColorIndex = xlColorIndexNone

 

[PivotMeThis]

Wouldn't this just change the cell that's selected back to black? I'm looking for something that I can put in the workbook code and apply to a button that will change all text back to black when she is done editing. Check boxes for each contract would be great but one button for the whole sheet would be adequate.

Excuse me if I don't "get it". But I don't

 

[shg]

Then you don't want to do it in the Change event.

Sub BackToBlack()
  With Worksheets("ProjectData")
    .Unprotect
    Cells.Font.ColorIndex = xlColorIndexNone
    .Protect
  End With
End Sub

 

[shg]

[B][COLOR="#FF0000"][SIZE=4].[/SIZE][/COLOR][/B]Cells.Font.ColorIndex = xlColorIndexNone

 

[PivotMeThis]

Thanks!
I made a couple minor changes to get it to work

Sub BackToBlack()
With Sheets("ProjectData")
.Unprotect
Cells.Font.ColorIndex = xlColorIndexAutomatic
.Protect
End With
End Sub