Darno,
Is this what you mean
Sub AAtester10()
Dim sStart As String, sEnd As String
Dim res As Variant, res1 As Variant
Dim rng As Range
sStart = InputBox("Enter Start Date")
sEnd = InputBox("Enter End Date")
If IsDate(sStart) And IsDate(sEnd) Then
res = Application.Match(CLng(CDate(sStart)), Range("B1:B800"), 0)
res1 = Application.Match(CLng(CDate(sEnd)), Range("B1:B800"), 0)
If Not IsError(res) And Not IsError(res1) Then
Set rng = Range(Range("B1:B800")(res), Range("B1:B800")(res1))
rng.Resize(, 31).BorderAround Weight:=xlMedium, ColorIndex:=3
Set rng = rng(1, 1).Offset(0, -1).Resize(rng.Count)
rng(1, 1).FormulaR1C1 = "=R[-1]C+1"
rng(1, 1).AutoFill rng
rng.Copy
rng.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If
End If
End Sub
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"darno " wrote in message
...
Hi,
Following Macro was written to me by TOM, it worked great, I have to
make few modifications in this macro. As this macros asks for two
inputs and highlight the required range area, I also would like to see
an auto generated number list from 1 to any number (depending on the
2nd input) in Column A, exactly next to Date cell, i.e., if the first
date that I entered as an input was 01-May-2003 and was stored in cell
B26 and the second date (ending date) was 10-may-2003 and was stored in
Cell B35, now the total rows involved between these two dates are 10,
now what I need is a counting from One to Ten but the counting must
start in column A26 which should be adjacent to forst input cell i.e.,
A26 and the last number of counting should be next to the end date
i.e., A35. This list generating procedure must be dependent on first
date input and second date input.
RESULT DATE COLUMN
ColumnA ColumnB
1 01-May-03
2 02-May-03
3 03-May-03
4 04-May-03
5 05-May-03
6 06-May-03
7 07-May-03
8 08-May-03
9 09-May-03
10 10-May-03
Regards,
DARNO
Sub AAtester10()
Dim sStart As String, sEnd As String
Dim res As Variant, res1 As Variant
Dim rng As Range
sStart = InputBox("Enter Start Date")
sEnd = InputBox("Enter End Date")
If IsDate(sStart) And IsDate(sEnd) Then
res = Application.Match(CLng(CDate(sStart)), Range("B1:B800"), 0)
res1 = Application.Match(CLng(CDate(sEnd)), Range("B1:B800"), 0)
If Not IsError(res) And Not IsError(res1) Then
Set rng = Range(Range("B1:B800")(res), Range("B1:B800")(res1))
rng.Resize(, 31).BorderAround Weight:=xlMedium, ColorIndex:=3
End If
End If
End Sub
---
Message posted from http://www.ExcelForum.com/