Modelling and Formatting

Jimmy Tsawo

New member
Joined
Feb 18, 2014
Messages
4
Reaction score
0
Points
0
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.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    On Error GoTo ws_exit
    
    Application.EnableEvents = False
    
    If Not Intersect(Target, Me.Columns("A")) Is Nothing Then
    
        Target.Offset(0, 1).NumberFormat = Target.NumberFormat
        Target.Offset(0, 2).NumberFormat = Target.NumberFormat
    End If


ws_exit:
    Application.EnableEvents = True
End Sub
 

Jimmy Tsawo

New member
Joined
Feb 18, 2014
Messages
4
Reaction score
0
Points
0
I have used the following formula and it worked very well.

=AND(CELL("format",A1)=CELL("format",B1),CELL("for mat",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 in all the columns?
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
How does that formula do what you originally asked for, set the cell's format? It just tells you if they are the same.
 
Top