Calander

[pwill]

Hi can any help with a formula to highlight text within a range that is equal to todays date

eg Range H4:N9

todays date is 28th Jan so the value in cell N8 (28) is auto highlighted in red

M	T	W	T	F	S	S
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31

<colgroup><col style="mso-width-source:userset;mso-width-alt:1097; width:23pt" span="7" width="30"> </colgroup><tbody>
</tbody>

any help would be apprieciated

regards

pwill

 

[Fluff]

You could CF with this formula
=H4=DAY(TODAY())

 

[pwill]

Thankyou Fluff, that worked great.

much appriciated

regards,

pwill

 

[Fluff]

Glad to help & thanks for the feedback

 

[My Aswer Is This]

Pwill:

I have helped you with other things.
Here is a little script I wrote which will put a calendar on your sheet and highlight todays date.

Just thought you may want to try it,
I store it in my Personal Folder so it's always available no matter what workbook I have open.

Sub My_Calendar()
'Version 1 9/23/15
ans = InputBox("What Range Should First Cell Be ?" & vbNewLine & "If left empty will use active cell", , "E7")
If ans = "" Then ans = ActiveCell.Address
Range(ans).Resize(5, 7).Select
Selection.Name = "Month"
Range("Month").Font.Size = 16
Dim z As Integer
z = 0
Dim Del As Variant
Del = Array("Sun", "Mon", "Tue", "Wed", "Thur", "Fri", "Sat")
MN = DateSerial(Year(Date), Month(Date), 1)
    'MsgBox Month
    For Each c In Range("Month")
        
        'MsgBox MN
        'c.Value = (MN - Weekday(Cells(1, 1).Value - 1) + Count)
        c.Value = (MN - Weekday(MN - 1) + Count)
        
        
        Count = Count + 1
    Next
    With Range("Month")
       
        .Interior.ColorIndex = 5
        .Find(What:=Date).Select
        ActiveCell.Interior.ColorIndex = 4
        End With
z = -1
Range("Month").Offset(-1).Resize(1, 7).Select
    For Each c In Selection
    z = z + 1
    c.Value = Del(z)
    Next
Range("Month").Columns.AutoFit
Selection.Interior.ColorIndex = 6
Selection.Font.Size = 16
Selection.HorizontalAlignment = xlCenter
Range("Month").NumberFormat = "m/d/yy"
Range("Month")(1).Select
End Sub

 

[pwill]

Hi My Aswer Is This,

Thank you for that, I was just introducing my daughter to excel and she wanted to make a calender so your code will be a big help to us

I know its a bit late but I have been waiting all day for my daugher to finish her calender lol

You said you would help me with adding the 'For i =' into my macro to help me understand how it works and so I have put together the best I can to show you what I trying to do.

I add data to sheet1 - Tab name(Updates) and copy over the data to sheet2 - Tab name(Data)

On Sheet1 I have headers A1:G1 as A B C D E F G H and I add rows of data from A2:G
On Sheet2 I have Dates that run from A2:A with a header in A1, and a count that runs from D2:D
E2:L on sheet2 has data copied from Sheet1 with headers E1:K1 as A B C D E F G and H1 Used
AI2:AQ2 on sheet2 are linked to Columns A2: D2: E2: F2: G2: H2: I2: J2: K2 so I can auto fill down the data from the columns A2:K

The data copied from Sheet1 is added to the last row E:K on Sheet2 and then I autofill colums AI2:AQ2 down the number of rows added from Sheet1.
So if I copied A2:G16 15 rows would be added to columns E:K from the last row.

I will add examples after this post.
In my example, Columns E2:K12 Sheet2 already has data, so if I copy the rows from Sheet1, the data will run E2:K26.

I am trying to add code to the macro that copies the data from Sheet1 to include autofill Columns AI2:AQ2 down the number of rows from Sheet1 A2:K with data.

 

[pwill]

eg

Sheet2 with the data already

Date/Year				A	B	C	D	E	F	G	used																							Date/Year		A	B	C	D	E	F	G
01/01/18			1	1	2	3	4	5	6	7	1																							10/01/18	10	1	2	3	4	5	6	7
02/01/18			2	1	2	3	4	5	6	7	1																							11/01/18	11	0	0	0	0	0	0	0
03/01/18			3	1	2	3	4	5	6	7	1
04/01/18			4	1	2	3	4	5	6	7	1
05/01/18			5	1	2	3	4	5	6	7	1
06/01/18			6	1	2	3	4	5	6	7	1
07/01/18			7	1	2	3	4	5	6	7	1
08/01/18			8	1	2	3	4	5	6	7	1
09/01/18			9	1	2	3	4	5	6	7	1
10/01/18			10	1	2	3	4	5	6	7	1
11/01/18			11	0	0	0	0	0	0	0
12/01/18			12
13/01/18			13
14/01/18			14
15/01/18			15
16/01/18			16
17/01/18			17
18/01/18			18
19/01/18			19
20/01/18			20
21/01/18			21
22/01/18			22
23/01/18			23
24/01/18			24
25/01/18			25

<tbody>
</tbody>

I will add more examples next

 

[pwill]

Sheet1 with the data to be copied

A	B	C	D	E	F	G
1	2	3	4	5	6	7
1	2	3	4	5	6	7
1	2	3	4	5	6	7
1	2	3	4	5	6	7
1	2	3	4	5	6	7
1	2	3	4	5	6	7
1	2	3	4	5	6	7
1	2	3	4	5	6	7
1	2	3	4	5	6	7
1	2	3	4	5	6	7
1	2	3	4	5	6	7
1	2	3	4	5	6	7
1	2	3	4	5	6	7
1	2	3	4	5	6	7
0	0	0	0	0	0	0

<tbody>
</tbody>

Sheet2 with the data added from Sheet1 and Autofilled down

Date/Year				A	B	C	D	E	F	G	used																							Date/Year		A	B	C	D	E	F	G
01/01/18			1	1	2	3	4	5	6	7	1																							10/01/18	10	1	2	3	4	5	6	7
02/01/18			2	1	2	3	4	5	6	7	1																							11/01/18	11	1	2	3	4	5	6	7
03/01/18			3	1	2	3	4	5	6	7	1																							12/01/18	12	1	2	3	4	5	6	7
04/01/18			4	1	2	3	4	5	6	7	1																							13/01/18	13	1	2	3	4	5	6	7
05/01/18			5	1	2	3	4	5	6	7	1																							14/01/18	14	1	2	3	4	5	6	7
06/01/18			6	1	2	3	4	5	6	7	1																							15/01/18	15	1	2	3	4	5	6	7
07/01/18			7	1	2	3	4	5	6	7	1																							16/01/18	16	1	2	3	4	5	6	7
08/01/18			8	1	2	3	4	5	6	7	1																							17/01/18	17	1	2	3	4	5	6	7
09/01/18			9	1	2	3	4	5	6	7	1																							18/01/18	18	1	2	3	4	5	6	7
10/01/18			10	1	2	3	4	5	6	7	1																							19/01/18	19	1	2	3	4	5	6	7
11/01/18			11	1	2	3	4	5	6	7	1																							20/01/18	20	1	2	3	4	5	6	7
12/01/18			12	1	2	3	4	5	6	7	1																							21/01/18	21	1	2	3	4	5	6	7
13/01/18			13	1	2	3	4	5	6	7	1																							22/01/18	22	1	2	3	4	5	6	7
14/01/18			14	1	2	3	4	5	6	7	1																							23/01/18	23	1	2	3	4	5	6	7
15/01/18			15	1	2	3	4	5	6	7	1																							24/01/18	24	1	2	3	4	5	6	7
16/01/18			16	1	2	3	4	5	6	7	1																							25/01/18	25	0	0	0	0	0	0	0
17/01/18			17	1	2	3	4	5	6	7	1
18/01/18			18	1	2	3	4	5	6	7	1
19/01/18			19	1	2	3	4	5	6	7	1
20/01/18			20	1	2	3	4	5	6	7	1
21/01/18			21	1	2	3	4	5	6	7	1
22/01/18			22	1	2	3	4	5	6	7	1
23/01/18			23	1	2	3	4	5	6	7	1
24/01/18			24	1	2	3	4	5	6	7	1
25/01/18			25	0	0	0	0	0	0	0

<tbody>
</tbody>

I will add the macro I am using next

 

[pwill]

This is the macro I am tring to add code to and I am trying to use 'For i = 2 to lRow Sheet1' as the number of rows to autofill on Sheet2

Sub Copy_Data()

Dim wsUpdates As Worksheet: Set wsUpdates = Sheet00
Dim wsData As Worksheet: Set wsData = Sheet01
Dim lRow As Long
Dim lRowK As Long
Dim lRowAS As Long
    
    Application.ScreenUpdating = False
    
    lRow = wsUpdates.Cells(Rows.Count, "A").End(xlUp).Row
    lRowK = wsData.Cells(Rows.Count, "K").End(xlUp).Row
    lRowAS = wsData.Cells(wsData.Rows.Count, "AS").End(xlUp).Row
    
wsUpdates.Activate
        Range("H2:H" & lRow).Value = 1
        
        Range("A" & lRow + 1).Value = 0
        Range("B" & lRow + 1).Value = 0
        Range("C" & lRow + 1).Value = 0
        Range("D" & lRow + 1).Value = 0
        Range("E" & lRow + 1).Value = 0
        Range("F" & lRow + 1).Value = 0
        Range("G" & lRow + 1).Value = 0
        
        Range("A2:H" & lRow + 1).Copy
        
        Range("A1").Select
        
    wsData.Activate
        Range("E" & lRowK).PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        
        'For i = 2 To lRow
        'Range("AI2:AQ2").AutoFill Destination:=Range("AI2:AQ" + i)
        'Next
        
        wsUpdates.Range("A2:H" & lRow + 1).ClearContents
        
        Application.ScreenUpdating = True
        
End Sub

The part in green is what obviously where I am trying to add the 'For i ='

Any help would be apprieciated

regards

pwill

 

[pwill]

I should have said

Sheet1 with the data to be copied before the zero's are added with the macro

A	B	C	D	E	F	G
1	2	3	4	5	6	7
1	2	3	4	5	6	7
1	2	3	4	5	6	7
1	2	3	4	5	6	7
1	2	3	4	5	6	7
1	2	3	4	5	6	7
1	2	3	4	5	6	7
1	2	3	4	5	6	7
1	2	3	4	5	6	7
1	2	3	4	5	6	7
1	2	3	4	5	6	7
1	2	3	4	5	6	7
1	2	3	4	5	6	7
1	2	3	4	5	6	7

<tbody>
</tbody>