Change of cell format if overwritten by User
 

I am close to an answer but lack of knowledge is frustrating me. The event
below is fired by a prompt
(suggested by a formula) being overwritten by a user. The prompt is half
grey by default, but
if the 'suggestion' is accepted by the user, it is overtyped and the code
makes it 'Bold & Black'.
If I limit the range to just cell C8, it works a treat (if slowly) but I
need this to occur individually
for each cell C8,C13,C18,C23 and C28. The code below maks them all 'Bold &
Black' as soon
as the first cell is changed ! Please help me (and suggest how to speed the
thing up !)
Graham B

Private Sub Worksheet_Change(ByVal Target As Range)

' Macro recorded 22/02/2007 by Me.

' This macro keeps default format (half grey) for Overtime cells

' if they are not changed, but makes them Bold and Black if overwritten by
user



If Intersect(Target, Range("C8,C13,C18,C23,C28")) Is Nothing Then

Exit Sub

End If

With Range("C8,C13,C18,C23,C28")

If .HasFormula Then

Exit Sub

End If

.ClearFormats

.Font.FontStyle = "bold"

.Font.ColorIndex = xlAutomatic

.NumberFormat = "h:mm"

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

' .WrapText = False

' .Orientation = 0

' .AddIndent = False

' .ShrinkToFit = False

' .MergeCells = False

End With

End Sub

Change of cell format if overwritten by User
 

change

With Range("C8,C13,C18,C23,C28")

to

With Target

Also, I *think* you want to disable events at the beginning & re-enable at
the end

"GrahamB" wrote:

I am close to an answer but lack of knowledge is frustrating me. The event
below is fired by a prompt
(suggested by a formula) being overwritten by a user. The prompt is half
grey by default, but
if the 'suggestion' is accepted by the user, it is overtyped and the code
makes it 'Bold & Black'.
If I limit the range to just cell C8, it works a treat (if slowly) but I
need this to occur individually
for each cell C8,C13,C18,C23 and C28. The code below maks them all 'Bold &
Black' as soon
as the first cell is changed ! Please help me (and suggest how to speed the
thing up !)
Graham B

Private Sub Worksheet_Change(ByVal Target As Range)

' Macro recorded 22/02/2007 by Me.

' This macro keeps default format (half grey) for Overtime cells

' if they are not changed, but makes them Bold and Black if overwritten by
user



If Intersect(Target, Range("C8,C13,C18,C23,C28")) Is Nothing Then

Exit Sub

End If

With Range("C8,C13,C18,C23,C28")

If .HasFormula Then

Exit Sub

End If

.ClearFormats

.Font.FontStyle = "bold"

.Font.ColorIndex = xlAutomatic

.NumberFormat = "h:mm"

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

' .WrapText = False

' .Orientation = 0

' .AddIndent = False

' .ShrinkToFit = False

' .MergeCells = False

End With

End Sub