Is it possible to format cell using a specific cell reference?

[b624333]

Hello,

I have a workbook full of differnt dates. To make entering data (the dates) quicker, I had previously thought of formatting the cell in the following way so that it was possible to enter only a number to turn into a date: "June" 0", 2014"

Using the above formatting, I could enter 27 only and it work format correctly to June 27, 2014.

However, the problem I am running into now is when the month changes. It is annoying to change the format of the cells every month.

Is it possible to format a cell using what is in a certain cell? For example, if I type "July" into A2 and "2015" into A3, could I somehow use those cells to format the cell correctly when only entering in a number like above.

Let me know if this is clear.

[6StringJazzer]

Yes but it would require a macro.

If you type

6/27

it will always assume you mean the current year. That's not too bad unless you are spending your entire day entering dates.

There are other ways you can do this, depending on what your data looks like. Can you share your file?

[FDibbins]

Why not use helper columns, 1 to enter the day, and the other with a formula, as follows...

	A	B	C
2		Day	Month
3	1/1/2014	1	1
4	1/2/2014	2	1
5	1/3/2014	3	1
6	1/4/2014	4	1
7	1/5/2014	5	1
8	1/6/2014	6	1
9	1/7/2014	7	1
10	1/8/2014	8	1
11	1/9/2014	9	1
12	1/10/2014	10	1

A3=DATE(2014,C3,B3) copied down
B3 is where you enter the date
C4=C3 copied down
When the month changes, you 3nter the new number once and it gets adjusted down

[6StringJazzer]

Here is a macro solution, if I've understood your question properly.

[b624333]

That works well.

Could we use the same princinples in the attached workbook? I have 8 columns where dates like this need to be entered. :"Blank columns" are purposefully left blank.

Additionally, I would liek to select the month and year from another sheet.

Is it possible to adapt your VBA code to the above two specifications?

[b624333]

6StringJazzer,

I realize I could be a frustrating user, but I would love your help with my problem.

Again, I would like to adapt your VBA code to do two additional things:
1. Extend it to columns 1, 3, 5, 7, 9, 11, 13, and 15.
2. Select the Month and Year from a Different Sheet (Sheet 2 in the example file I attached above).

[6StringJazzer]

We are on the line between providing help and doing your project for you. What is your level of VBA ability? I would prefer to answer specific VBA code questions if you have them.

[b624333]

That is certainly not my intention. I am self-teaching myself (as I do a lot of things). I apologize, but I learn best by working through examples. I have edited your code to the following:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim ErrMsg As String
   
   Dim C As Range
   
   For Each C In Target
   
      If C.Column = 1 And C.Row > 1 And C <> "" Then
      
         If Sheets("Sheet2").Range("F1") = "" Then
            ErrMsg = "You must enter a month where shown"
         End If
         
         If Sheets("Sheet2").Range("F2") = "" Then
            ErrMsg = ErrMsg & vbCrLf & "You must enter a year where shown"
         End If
         
         If ErrMsg <> "" Then
            MsgBox ErrMsg
         Else
         
            If C > Day(DateSerial(Sheets("Sheet2").Range("F2"), Sheets("Sheet2").Range("G1") + 1, 1) - 1) Or C <= 0 Then
               MsgBox "You have entered a day of the month that is not valid for the month you entered."
               C.NumberFormat = "General"
            Else
               Application.EnableEvents = False
               C = DateSerial(Sheets("Sheet2").Range("F2"), Sheets("Sheet2").Range("G1"), C)
               Application.EnableEvents = True
            End If
         
         End If
         
      End If
   
   Next C
   
End Sub

My specific VBA code question is: How would I extend this code to multiple columns?

I tried

If C.Column = 1,3,5 And C.Row > 1 And C <> "" Then

but that did not work. I've never seen a range defined the way you did it, so I am just lost. I would appreciate any help you might be able to give me.

[6StringJazzer]

If (C.Column = 1 Or C.Column = 3 Or C.Column = 5) And C.Row > 1 And C <> "" Then

Read up on logical operators. It reads like you thought up in your head what you wanted to do and wrote it down without researching how you actually do that in VBA.