Reply to thread

Message: [QUOTE="Norman Jones, post: 7426688"] Hi TSN, Unfortunately, there is no event which can be captured in response to a change of number format. However, taking advantage of the worksheet calculation event, try:- Right-click on the worksheet tab | Select the option: View Code Paste the following code '===========>> Option Explicit '----------->> Private Sub Worksheet_Calculate() Dim Rng As Range Dim sFormat As String Dim myErr As Long Set Rng = Intersect(Columns("A:A"), Me.UsedRange) On Error Resume Next sFormat = Rng.NumberFormat myErr = Err.Number On Error GoTo 0 If myErr = 94 Then Call MsgBox(Prompt:="Check " & Rng.Address(0, 0) & " for multiplenumber formats!", _ Buttons:=vbCritical, _ Title:="Format Problem") Else Rng.Offset(0, 1).Resize(, 2).NumberFormat = sFormat End If End Sub '<<=========== In cell D1 enter the formula =AND(CELL("format",A1)=CELL("format",B1),CELL("format",A1)=CELL("format",C1)) and drag down as far as required. This code will automatically update the number format of columns B and C toaccord with that of Column A. If Column A contains more than one number format, an error message will be produced. Since the suggested code and formulas rely on the calculation of the worksheet, you should ensure that calculation is set to automatic or use F9 to force arecalculation. === Regards, [/QUOTE]