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

<colgroup><col style="mso-width-source:userset;mso-width-alt:1097; width:23pt" span="7" width="30"> </colgroup><tbody>
[TD="class: xl65, width: 30, align: center"] M
[/TD]
[TD="class: xl65, width: 30, align: center"] T [/TD]
[TD="class: xl65, width: 30, align: center"] W [/TD]
[TD="class: xl65, width: 30, align: center"] T [/TD]
[TD="class: xl65, width: 30, align: center"] F [/TD]
[TD="class: xl65, width: 30, align: center"] S [/TD]
[TD="class: xl65, width: 30, align: center"] S [/TD]

[TD="class: xl63, align: center"] 1 [/TD]
[TD="class: xl64, align: center"] 2 [/TD]
[TD="class: xl64, align: center"] 3 [/TD]
[TD="class: xl64, align: center"] 4 [/TD]
[TD="class: xl64, align: center"] 5 [/TD]
[TD="class: xl64, align: center"] 6 [/TD]
[TD="class: xl64, align: center"] 7 [/TD]

[TD="class: xl64, align: center"] 8 [/TD]
[TD="class: xl64, align: center"] 9 [/TD]
[TD="class: xl64, align: center"] 10 [/TD]
[TD="class: xl64, align: center"] 11 [/TD]
[TD="class: xl64, align: center"] 12 [/TD]
[TD="class: xl64, align: center"] 13 [/TD]
[TD="class: xl64, align: center"] 14 [/TD]

[TD="class: xl64, align: center"] 15 [/TD]
[TD="class: xl64, align: center"] 16 [/TD]
[TD="class: xl64, align: center"] 17 [/TD]
[TD="class: xl64, align: center"] 18 [/TD]
[TD="class: xl64, align: center"] 19 [/TD]
[TD="class: xl64, align: center"] 20 [/TD]
[TD="class: xl64, align: center"] 21 [/TD]

[TD="class: xl64, align: center"] 22 [/TD]
[TD="class: xl64, align: center"] 23 [/TD]
[TD="class: xl64, align: center"] 24 [/TD]
[TD="class: xl64, align: center"] 25 [/TD]
[TD="class: xl64, align: center"] 26 [/TD]
[TD="class: xl64, align: center"] 27 [/TD]
[TD="class: xl64, align: center"] 28
[/TD]

[TD="class: xl64, align: center"] 29 [/TD]
[TD="class: xl64, align: center"] 30 [/TD]
[TD="class: xl64, align: center"] 31 [/TD]
[TD="class: xl64, align: center"]
[/TD]
[TD="class: xl64, align: center"]
[/TD]
[TD="class: xl64, align: center"] [/TD]
[TD="class: xl64, align: center"]
[/TD]

</tbody>

any help would be apprieciated

regards

pwill

 

[My Aswer Is This]

Not sure what your wanting help with.
But if all your wanting is to put zeros in column A to G of the lastrow+1 you can use this:
You need no loop.

Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Range(Cells(Lastrow, "A"), Cells(Lastrow, "G")).Value = "0"

 

[My Aswer Is This]

If you insist and want a loop it would be like this:

Sub I_Want_A_Loop()
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
    For i = 1 To 7
        Cells(Lastrow, i).Value = "0"
    Next
End Sub

 

[My Aswer Is This]

If you want to enter all this years dates in column (A) and in column (B) put the dates day.

Try this script:

Sub Enter_Dates_Column_A()
Dim i As Long
    For i = 1 To 365
    
    Cells(i, 1).Value = DateAdd("d", i - 1, "1/1/2018")
    Cells(i, 2).Value = Day(Cells(i, 1))
    
Next
End Sub

 

[pwill]

Hi My Aswer Is This,

Sorry if I have confused things, it would be so much easier if I could upload a workbook example.

Basically it's the Auto fill I am struggling with. I add rows of data A2:G sheet1 this could be any amount of rows. Then the macro adds the zero's for me to the last empty rows A:Gand the 1's in column H These rows are then copied to sheet2 over writing the last rows in columns E:L Columns A and D sheet2 already have dates and count pre set.
Then on then sheet2 AI:AQ are linked to columns A, D & E:K sheet2 to duplicate the data in these column when dragging down. when I drag down from the last rows in columns AI:AQ I want to Auto fill the amount of rows copied from sheet1. So if 10 rows where added from sheet1 the auto fill in AI:AQ would fill down 10 rows from the last row in AI:AQ.

Does this make more sense? It's long winded I know but if I could upload a workbook example you would think it is really very simple.

Hope this helps?

Regards

pwill

 

[pwill]

Sorry I just noticed the sheets where numbered wrong in the macro I gave, they should have been

Dim wsUpdates As Worksheet: Set wsUpdates = Sheet01 Dim wsData As Worksheet: Set wsData = Sheet02

sorry about that

pwill

 

[My Aswer Is This]

I'm sorry but this is more complicated then I thought. I thought you were just learning Vba and wanting help creating a loop. And I'm not familiar with "AutoFill".
I did notice in a earlier post you were wanting to renumber your sheets also. So I'm not familiar with how you can have a sheet numbered Sheet00. You could name a sheet "Sheet00" but how you can number a sheet as 00 is also something I'm not familiar with.
Please excuse my ignorance on these issues. I'm hoping someone else here at Mr. Excel will be able to help you. I will continue to monitor this thread to see what I can learn.

 

[My Aswer Is This]

I did look into Autofill and suppose the reason you are using this is because you have formulas in these cells.

 

[pwill]

Hi, yes that's exactly right, the formulas in these cells arrange the data the way I want it when I auto fill down.

Your help is always appreciated.

Regards

 

[My Aswer Is This]

I assume you know a lot of times if your using Vba instead of putting formulas in cells you can have Vba put the results of a formulas into a cell and really need no formula in the cell.

Cells(1,3).value=Cells(1,1).value+Cells(1,2).value
And put that into a loop.

 

[pwill]

Hi, thanks My Aswer Is This,

Yes I know you can do that sort thing with Vba, I probably will when I get things to how I want them. Just so you know I wouldn't say i'm a newby but I am still learning, I pretty much know the basics but compared to others on here I am just scratching the surface. Sorry about any confusion with my posts, I will keep trying to find the answer I am looking for and if I do I will let you know what the solution turns out to be.

As for re numbering my sheets, you probably already know how to, its just me complicating things but I will explain anyway.

In the Visual Basic window on the left where the project window is, if you click view properties window you can change the name of the sheet, all I did was add a '0' before the 1 ie sheet1 to sheet01 and I did the same with another sheet but changed it to 'sheet00' then when you look at the project window you will see the sheet name has changed to Sheet01(Tab Name) but i'm sure you already knew that lol

Regards

pwill