Unable to figure out a macro to retain font in grey but change to black once overwritten

[nmenon]

Hi,

I am very new to the whole VBA deal and need someone to help me out from scratch. I have attached the workbook here. In the workbook, i require someone to help me with the font colors in Column C (4:11) and Column D (24:46). Basically what needs to happen is the text that is currently present needs to be greyed out but ever present. However, when text is input in these cells, the font needs to change to black. Does anyone have any idea how this can be achieved?

Secondly, the comments in column D (Please provide comments) needs to turn off and turn on based on the choice made in the Yes/No column.

Lastly, could someone help me with the macro to enable a sign off. As in, once the Sign Off button, which is at the end of the file is clicked, a pop up needs to present itself enabling the user to enter his/her name, there by signing off on the document.

Thanks in advance!

[scott.s.fower]

the code goes on the sheet1 code, right click the tab and select view code

Private Sub Worksheet_Change(ByVal Target As Range)


With Range("D23:D46")
    If Target.Value <> "Please Provide Comments" And Target.Offset(, -1).Value = "No" Then
        With Target.Font
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
        End With
    Else:
        With Target.Font
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.149998474074526
        End With
    Target.FormulaR1C1 = "=IF(RC[-1]=""No"", ""Please Provide comments"","" "")"
    End If

End With

End Sub