Formatting and modelling

[tsnrbc]

I am currently undertaking an exercise at varsity. I have 3 columns, with base, target, and actual. these 3 columns can be in percentages, actual numbers, dates, or currency. For example, base is 1-Jan-2013; target is 31-Dec-2013. The actual date is 20-Dec-2013.

I would like the second and third columns to automatically change to the format of the first column, for example, if the first column is in percentage(%), the remaining two columns automatically change to percentage.

is there a way of creating an error message on the second and third column if they do not match the format of the first column?

Thank you in advance those who can assist.

 

[Norman Jones]

I am currently undertaking an exercise at varsity. I have 3 columns, withbase, target, and actual. these 3 columns can be in percentages, actual numbers, dates, or currency. For example, base is 1-Jan-2013; target is 31-Dec-2013. The actual date is 20-Dec-2013.



I would like the second and third columns to automatically change to the format of the first column, for example, if the first column is in percentage (%), the remaining two columns automatically change to percentage.



is there a way of creating an error message on the second and third column if they do not match the format of the first column?



Thank you in advance those who can assist.

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
'<<===========

is there a way of creating an error message on the second and third column if they do not match the format of the first column?

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,

 

[tsnrbc]

I have used the formula below that you provided. It worked perfectly. Thank you very much.

=AND(CELL("format",A1)=CELL("format",B1),CELL("format",A1)=CELL("format",C1))

My next question is as follows:
I want to add another column with a dropdown of the different units of measurement, eg %, dates, numbers, etc.

How can I use this formula to detect different formats inthe following columns?