Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In excel how do I protect the format of a cell but let the user c. | Excel Discussion (Misc queries) | |||
Does a format in Excel change when another user opens the file? | Excel Discussion (Misc queries) | |||
How do I protect a cell formula from being overwritten | Excel Discussion (Misc queries) | |||
How do I format a cell, so the user must use a user calendar? | Excel Discussion (Misc queries) | |||
How do I format a cell, so the user must use a user calendar? | Excel Discussion (Misc queries) |