Ok here is the situation I am having.
I have a worksheet named Sheet1 which column "A" has a bunch of dates
starting Jan 1, 2000 and going down. Column "C" Also has a bunch of dates
starting with Jan 1, 2000 and going down. Column "D" and "E" have just
regular numerical data. Column "A" is based on 5 day week and colunm "C" is
based on 7 day week. and Row 1 is column hedders. What My codde should do
when run is highlight all the dates in column "C" that match the dates in
Column "A" and the adjacent columns which contain regular numerical data.
My Dates in column "A" run from Jan 1, 2000 to Feb 29, 2000 (5 day weekday
series)
My Dates in Column "C" run from Jan 1, 2000 to Mar 20, 2002 (7 day series)

When my code is run it misses highlighting cell "C2" which should be a
matching date with cell "A2" also in colun "C" Nov 11, 2000 is highlighted as
a matching date but doesn't exist in column "A". ..... Anyhow here is my
code.

Sub Test()
Dim BaseDateRng As Range
Dim SecondDateRng As Range
Dim cel As Range
Dim c As Range
Dim bDate As Date
Dim d As Long

Set BaseDateRng = Worksheets("Sheet1").Range("a2:a44")
'Range(frm1.RefBaseDate)

Set SecondDateRng = Worksheets("Sheet1").Range("c2:e811")
'Range(frm1.RefSecondDate)

For Each cel In BaseDateRng
bDate = cel.Value
With SecondDateRng.Columns(1)
Set c = .Find(bDate, LookIn:=xlFormulas)
d = c.Row - 1
If Not c Is Nothing Then
With SecondDateRng
.Rows(d).Interior.ColorIndex = 4
End With
End If
End With
Next cel
End Sub

Dan,

Use conditional formatting.

Select all the cells in column C,D & E, from row 2 until the end
Goto menu FormatConditional Formatting
Change Condition 1 to Formula Is
Add the formula =AND($C2<"",ISNUMBER(MATCH($C2,$A:$A,0)))
Selct the pattern tab and choose a colour
OK out

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dan Thompson" wrote in message
...
Ok here is the situation I am having.
I have a worksheet named Sheet1 which column "A" has a bunch of dates
starting Jan 1, 2000 and going down. Column "C" Also has a bunch of dates
starting with Jan 1, 2000 and going down. Column "D" and "E" have just
regular numerical data. Column "A" is based on 5 day week and colunm "C"
is
based on 7 day week. and Row 1 is column hedders. What My codde should do
when run is highlight all the dates in column "C" that match the dates in
Column "A" and the adjacent columns which contain regular numerical data.
My Dates in column "A" run from Jan 1, 2000 to Feb 29, 2000 (5 day weekday
series)
My Dates in Column "C" run from Jan 1, 2000 to Mar 20, 2002 (7 day series)

When my code is run it misses highlighting cell "C2" which should be a
matching date with cell "A2" also in colun "C" Nov 11, 2000 is highlighted
as
a matching date but doesn't exist in column "A". ..... Anyhow here is my
code.

Sub Test()
Dim BaseDateRng As Range
Dim SecondDateRng As Range
Dim cel As Range
Dim c As Range
Dim bDate As Date
Dim d As Long

Set BaseDateRng = Worksheets("Sheet1").Range("a2:a44")
'Range(frm1.RefBaseDate)

Set SecondDateRng = Worksheets("Sheet1").Range("c2:e811")
'Range(frm1.RefSecondDate)

For Each cel In BaseDateRng
bDate = cel.Value
With SecondDateRng.Columns(1)
Set c = .Find(bDate, LookIn:=xlFormulas)
d = c.Row - 1
If Not c Is Nothing Then
With SecondDateRng
.Rows(d).Interior.ColorIndex = 4
End With
End If
End With
Next cel
End Sub

Why not use conditional formatting

Select column C to E and do
Format=Conditional Formatting

C1 should be the activecell in the selection


change cell value is to Formula is

in the textbox put in

=countif(A:A,$C1)0

click the format button and then pattern tab. Select a color

OK out.

--
Regards,
Tom Ogilvy

"Dan Thompson" wrote in message
...
Ok here is the situation I am having.
I have a worksheet named Sheet1 which column "A" has a bunch of dates
starting Jan 1, 2000 and going down. Column "C" Also has a bunch of dates
starting with Jan 1, 2000 and going down. Column "D" and "E" have just
regular numerical data. Column "A" is based on 5 day week and colunm "C"
is
based on 7 day week. and Row 1 is column hedders. What My codde should do
when run is highlight all the dates in column "C" that match the dates in
Column "A" and the adjacent columns which contain regular numerical data.
My Dates in column "A" run from Jan 1, 2000 to Feb 29, 2000 (5 day weekday
series)
My Dates in Column "C" run from Jan 1, 2000 to Mar 20, 2002 (7 day series)

When my code is run it misses highlighting cell "C2" which should be a
matching date with cell "A2" also in colun "C" Nov 11, 2000 is highlighted
as
a matching date but doesn't exist in column "A". ..... Anyhow here is my
code.

Sub Test()
Dim BaseDateRng As Range
Dim SecondDateRng As Range
Dim cel As Range
Dim c As Range
Dim bDate As Date
Dim d As Long

Set BaseDateRng = Worksheets("Sheet1").Range("a2:a44")
'Range(frm1.RefBaseDate)

Set SecondDateRng = Worksheets("Sheet1").Range("c2:e811")
'Range(frm1.RefSecondDate)

For Each cel In BaseDateRng
bDate = cel.Value
With SecondDateRng.Columns(1)
Set c = .Find(bDate, LookIn:=xlFormulas)
d = c.Row - 1
If Not c Is Nothing Then
With SecondDateRng
.Rows(d).Interior.ColorIndex = 4
End With
End If
End With
Next cel
End Sub

I appreciate the Idea of conditional formating guy's, however 2 things..

1) I still don't know why the .Find method does not inclued the 1st cell in
the specified search range. (btw only seems to be a problem when working with
Date format)

2)I can't conditonaly format the cells do to the fact that my code is going
to be kind of dynamic in the sense that the ranges will not be fixed the will
be gathered from a EditRef control on a form by a user and the ranges /
worksheets / workbooks could be different every time the program is run.
Unless there is some way to have vba conditional format the ranges selected
by the user (real time) however that seems like a bunch of extra unessicary
steps when my code works fine already except for the one problme of the .Find
method not including the first cell in a range as a Match found.

Dan Thompson


"Tom Ogilvy" wrote:

Why not use conditional formatting

Select column C to E and do
Format=Conditional Formatting

C1 should be the activecell in the selection


change cell value is to Formula is

in the textbox put in

=countif(A:A,$C1)0

click the format button and then pattern tab. Select a color

OK out.

--
Regards,
Tom Ogilvy

"Dan Thompson" wrote in message
...
Ok here is the situation I am having.
I have a worksheet named Sheet1 which column "A" has a bunch of dates
starting Jan 1, 2000 and going down. Column "C" Also has a bunch of dates
starting with Jan 1, 2000 and going down. Column "D" and "E" have just
regular numerical data. Column "A" is based on 5 day week and colunm "C"
is
based on 7 day week. and Row 1 is column hedders. What My codde should do
when run is highlight all the dates in column "C" that match the dates in
Column "A" and the adjacent columns which contain regular numerical data.
My Dates in column "A" run from Jan 1, 2000 to Feb 29, 2000 (5 day weekday
series)
My Dates in Column "C" run from Jan 1, 2000 to Mar 20, 2002 (7 day series)

When my code is run it misses highlighting cell "C2" which should be a
matching date with cell "A2" also in colun "C" Nov 11, 2000 is highlighted
as
a matching date but doesn't exist in column "A". ..... Anyhow here is my
code.

Sub Test()
Dim BaseDateRng As Range
Dim SecondDateRng As Range
Dim cel As Range
Dim c As Range
Dim bDate As Date
Dim d As Long

Set BaseDateRng = Worksheets("Sheet1").Range("a2:a44")
'Range(frm1.RefBaseDate)

Set SecondDateRng = Worksheets("Sheet1").Range("c2:e811")
'Range(frm1.RefSecondDate)

For Each cel In BaseDateRng
bDate = cel.Value
With SecondDateRng.Columns(1)
Set c = .Find(bDate, LookIn:=xlFormulas)
d = c.Row - 1
If Not c Is Nothing Then
With SecondDateRng
.Rows(d).Interior.ColorIndex = 4
End With
End If
End With
Next cel
End Sub

If not cf then try the example in vba help for find NEXT

--
Don Guillett
SalesAid Software

"Dan Thompson" wrote in message
...
Ok here is the situation I am having.
I have a worksheet named Sheet1 which column "A" has a bunch of dates
starting Jan 1, 2000 and going down. Column "C" Also has a bunch of dates
starting with Jan 1, 2000 and going down. Column "D" and "E" have just
regular numerical data. Column "A" is based on 5 day week and colunm "C"
is
based on 7 day week. and Row 1 is column hedders. What My codde should do
when run is highlight all the dates in column "C" that match the dates in
Column "A" and the adjacent columns which contain regular numerical data.
My Dates in column "A" run from Jan 1, 2000 to Feb 29, 2000 (5 day weekday
series)
My Dates in Column "C" run from Jan 1, 2000 to Mar 20, 2002 (7 day series)

When my code is run it misses highlighting cell "C2" which should be a
matching date with cell "A2" also in colun "C" Nov 11, 2000 is highlighted
as
a matching date but doesn't exist in column "A". ..... Anyhow here is my
code.

Sub Test()
Dim BaseDateRng As Range
Dim SecondDateRng As Range
Dim cel As Range
Dim c As Range
Dim bDate As Date
Dim d As Long

Set BaseDateRng = Worksheets("Sheet1").Range("a2:a44")
'Range(frm1.RefBaseDate)

Set SecondDateRng = Worksheets("Sheet1").Range("c2:e811")
'Range(frm1.RefSecondDate)

For Each cel In BaseDateRng
bDate = cel.Value
With SecondDateRng.Columns(1)
Set c = .Find(bDate, LookIn:=xlFormulas)
d = c.Row - 1
If Not c Is Nothing Then
With SecondDateRng
.Rows(d).Interior.ColorIndex = 4
End With
End If
End With
Next cel
End Sub

Hi Bob,
Your conditional Format works for highlighting the right cells
can you involk conditional formating from vba code aswell ?

Also excuse my inexperience but I have never used condtional formating with
excel before.
I don't quit understand your formula even though it works.
=AND($C2<"",ISNUMBER(MATCH($C2,$A:$A,0)))
What is the "AND" for and why is $C2 specified but "$A" is not specified as
"$A2:$A44". and one more thing not sure what the ",0" is for either.

Sorry if my questions are dumb but I have no experience with the conditional
format menu command.

"Bob Phillips" wrote:

Dan,

Use conditional formatting.

Select all the cells in column C,D & E, from row 2 until the end
Goto menu FormatConditional Formatting
Change Condition 1 to Formula Is
Add the formula =AND($C2<"",ISNUMBER(MATCH($C2,$A:$A,0)))
Selct the pattern tab and choose a colour
OK out

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dan Thompson" wrote in message
...
Ok here is the situation I am having.
I have a worksheet named Sheet1 which column "A" has a bunch of dates
starting Jan 1, 2000 and going down. Column "C" Also has a bunch of dates
starting with Jan 1, 2000 and going down. Column "D" and "E" have just
regular numerical data. Column "A" is based on 5 day week and colunm "C"
is
based on 7 day week. and Row 1 is column hedders. What My codde should do
when run is highlight all the dates in column "C" that match the dates in
Column "A" and the adjacent columns which contain regular numerical data.
My Dates in column "A" run from Jan 1, 2000 to Feb 29, 2000 (5 day weekday
series)
My Dates in Column "C" run from Jan 1, 2000 to Mar 20, 2002 (7 day series)

When my code is run it misses highlighting cell "C2" which should be a
matching date with cell "A2" also in colun "C" Nov 11, 2000 is highlighted
as
a matching date but doesn't exist in column "A". ..... Anyhow here is my
code.

Sub Test()
Dim BaseDateRng As Range
Dim SecondDateRng As Range
Dim cel As Range
Dim c As Range
Dim bDate As Date
Dim d As Long

Set BaseDateRng = Worksheets("Sheet1").Range("a2:a44")
'Range(frm1.RefBaseDate)

Set SecondDateRng = Worksheets("Sheet1").Range("c2:e811")
'Range(frm1.RefSecondDate)

For Each cel In BaseDateRng
bDate = cel.Value
With SecondDateRng.Columns(1)
Set c = .Find(bDate, LookIn:=xlFormulas)
d = c.Row - 1
If Not c Is Nothing Then
With SecondDateRng
.Rows(d).Interior.ColorIndex = 4
End With
End If
End With
Next cel
End Sub

Find can be problematic with dates. I prefer Match

Sub Test()
Dim BaseDateRng As Range
Dim SecondDateRng As Range
Dim cel As Range
Dim c As Range
Dim bDate As Date
Dim d As Long
Dim res as Variant

Set BaseDateRng = Worksheets("Sheet1").Range("a2:a44")
'Range(frm1.RefBaseDate)

Set SecondDateRng = Worksheets("Sheet1").Range("c2:e811")
'Range(frm1.RefSecondDate)

For Each cel In BaseDateRng
bDate = cel.Value
With SecondDateRng.Columns(1)
res = Application.Match(clng(bDate), SecondDateRng.Columns(1),0)
if not iserror(res) then
set c = SecondDateRng.Columns(1).Cells(res)
d = c.Row - 1
With SecondDateRng
.Rows(d).Interior.ColorIndex = 4
End With
End If
End With
Next cel
End Sub

--
Regards,
Tom Ogilvy

"Dan Thompson" wrote in message
...
I appreciate the Idea of conditional formating guy's, however 2 things..

1) I still don't know why the .Find method does not inclued the 1st cell
in
the specified search range. (btw only seems to be a problem when working
with
Date format)

2)I can't conditonaly format the cells do to the fact that my code is
going
to be kind of dynamic in the sense that the ranges will not be fixed the
will
be gathered from a EditRef control on a form by a user and the ranges /
worksheets / workbooks could be different every time the program is run.
Unless there is some way to have vba conditional format the ranges
selected
by the user (real time) however that seems like a bunch of extra
unessicary
steps when my code works fine already except for the one problme of the
..Find
method not including the first cell in a range as a Match found.

Dan Thompson


"Tom Ogilvy" wrote:

Why not use conditional formatting

Select column C to E and do
Format=Conditional Formatting

C1 should be the activecell in the selection


change cell value is to Formula is

in the textbox put in

=countif(A:A,$C1)0

click the format button and then pattern tab. Select a color

OK out.

--
Regards,
Tom Ogilvy

"Dan Thompson" wrote in message
...
Ok here is the situation I am having.
I have a worksheet named Sheet1 which column "A" has a bunch of dates
starting Jan 1, 2000 and going down. Column "C" Also has a bunch of
dates
starting with Jan 1, 2000 and going down. Column "D" and "E" have just
regular numerical data. Column "A" is based on 5 day week and colunm
"C"
is
based on 7 day week. and Row 1 is column hedders. What My codde should
do
when run is highlight all the dates in column "C" that match the dates
in
Column "A" and the adjacent columns which contain regular numerical
data.
My Dates in column "A" run from Jan 1, 2000 to Feb 29, 2000 (5 day
weekday
series)
My Dates in Column "C" run from Jan 1, 2000 to Mar 20, 2002 (7 day
series)

When my code is run it misses highlighting cell "C2" which should be a
matching date with cell "A2" also in colun "C" Nov 11, 2000 is
highlighted
as
a matching date but doesn't exist in column "A". ..... Anyhow here
is my
code.

Sub Test()
Dim BaseDateRng As Range
Dim SecondDateRng As Range
Dim cel As Range
Dim c As Range
Dim bDate As Date
Dim d As Long

Set BaseDateRng = Worksheets("Sheet1").Range("a2:a44")
'Range(frm1.RefBaseDate)

Set SecondDateRng = Worksheets("Sheet1").Range("c2:e811")
'Range(frm1.RefSecondDate)

For Each cel In BaseDateRng
bDate = cel.Value
With SecondDateRng.Columns(1)
Set c = .Find(bDate, LookIn:=xlFormulas)
d = c.Row - 1
If Not c Is Nothing Then
With SecondDateRng
.Rows(d).Interior.ColorIndex = 4
End With
End If
End With
Next cel
End Sub

Thanks Tom I realy appreciate your help, and others aswell.

Dan.

"Tom Ogilvy" wrote:

Find can be problematic with dates. I prefer Match

Sub Test()
Dim BaseDateRng As Range
Dim SecondDateRng As Range
Dim cel As Range
Dim c As Range
Dim bDate As Date
Dim d As Long
Dim res as Variant

Set BaseDateRng = Worksheets("Sheet1").Range("a2:a44")
'Range(frm1.RefBaseDate)

Set SecondDateRng = Worksheets("Sheet1").Range("c2:e811")
'Range(frm1.RefSecondDate)

For Each cel In BaseDateRng
bDate = cel.Value
With SecondDateRng.Columns(1)
res = Application.Match(clng(bDate), SecondDateRng.Columns(1),0)
if not iserror(res) then
set c = SecondDateRng.Columns(1).Cells(res)
d = c.Row - 1
With SecondDateRng
.Rows(d).Interior.ColorIndex = 4
End With
End If
End With
Next cel
End Sub

--
Regards,
Tom Ogilvy

"Dan Thompson" wrote in message
...
I appreciate the Idea of conditional formating guy's, however 2 things..

1) I still don't know why the .Find method does not inclued the 1st cell
in
the specified search range. (btw only seems to be a problem when working
with
Date format)

2)I can't conditonaly format the cells do to the fact that my code is
going
to be kind of dynamic in the sense that the ranges will not be fixed the
will
be gathered from a EditRef control on a form by a user and the ranges /
worksheets / workbooks could be different every time the program is run.
Unless there is some way to have vba conditional format the ranges
selected
by the user (real time) however that seems like a bunch of extra
unessicary
steps when my code works fine already except for the one problme of the
..Find
method not including the first cell in a range as a Match found.

Dan Thompson


"Tom Ogilvy" wrote:

Why not use conditional formatting

Select column C to E and do
Format=Conditional Formatting

C1 should be the activecell in the selection


change cell value is to Formula is

in the textbox put in

=countif(A:A,$C1)0

click the format button and then pattern tab. Select a color

OK out.

--
Regards,
Tom Ogilvy

"Dan Thompson" wrote in message
...
Ok here is the situation I am having.
I have a worksheet named Sheet1 which column "A" has a bunch of dates
starting Jan 1, 2000 and going down. Column "C" Also has a bunch of
dates
starting with Jan 1, 2000 and going down. Column "D" and "E" have just
regular numerical data. Column "A" is based on 5 day week and colunm
"C"
is
based on 7 day week. and Row 1 is column hedders. What My codde should
do
when run is highlight all the dates in column "C" that match the dates
in
Column "A" and the adjacent columns which contain regular numerical
data.
My Dates in column "A" run from Jan 1, 2000 to Feb 29, 2000 (5 day
weekday
series)
My Dates in Column "C" run from Jan 1, 2000 to Mar 20, 2002 (7 day
series)

When my code is run it misses highlighting cell "C2" which should be a
matching date with cell "A2" also in colun "C" Nov 11, 2000 is
highlighted
as
a matching date but doesn't exist in column "A". ..... Anyhow here
is my
code.

Sub Test()
Dim BaseDateRng As Range
Dim SecondDateRng As Range
Dim cel As Range
Dim c As Range
Dim bDate As Date
Dim d As Long

Set BaseDateRng = Worksheets("Sheet1").Range("a2:a44")
'Range(frm1.RefBaseDate)

Set SecondDateRng = Worksheets("Sheet1").Range("c2:e811")
'Range(frm1.RefSecondDate)

For Each cel In BaseDateRng
bDate = cel.Value
With SecondDateRng.Columns(1)
Set c = .Find(bDate, LookIn:=xlFormulas)
d = c.Row - 1
If Not c Is Nothing Then
With SecondDateRng
.Rows(d).Interior.ColorIndex = 4
End With
End If
End With
Next cel
End Sub

Hi Bob,
Using the Conditional Formating works fine and I figured out most of the
questions
I asked in another post regarding your Match formula so you can ignore that
post.
However I do not understand why it is that if I use...
=AND($C2<"",ISNUMBER(MATCH($C2,$A1:$A811,0))), it does not work for
highlighting the all the appropriate matchs and yet if I use....
=AND($C2<"",ISNUMBER(MATCH($C2,$A:$A,0))) it works fine whats up with that ?

Dan.



"Bob Phillips" wrote:

Dan,

Use conditional formatting.

Select all the cells in column C,D & E, from row 2 until the end
Goto menu FormatConditional Formatting
Change Condition 1 to Formula Is
Add the formula =AND($C2<"",ISNUMBER(MATCH($C2,$A:$A,0)))
Selct the pattern tab and choose a colour
OK out

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dan Thompson" wrote in message
...
Ok here is the situation I am having.
I have a worksheet named Sheet1 which column "A" has a bunch of dates
starting Jan 1, 2000 and going down. Column "C" Also has a bunch of dates
starting with Jan 1, 2000 and going down. Column "D" and "E" have just
regular numerical data. Column "A" is based on 5 day week and colunm "C"
is
based on 7 day week. and Row 1 is column hedders. What My codde should do
when run is highlight all the dates in column "C" that match the dates in
Column "A" and the adjacent columns which contain regular numerical data.
My Dates in column "A" run from Jan 1, 2000 to Feb 29, 2000 (5 day weekday
series)
My Dates in Column "C" run from Jan 1, 2000 to Mar 20, 2002 (7 day series)

When my code is run it misses highlighting cell "C2" which should be a
matching date with cell "A2" also in colun "C" Nov 11, 2000 is highlighted
as
a matching date but doesn't exist in column "A". ..... Anyhow here is my
code.

Sub Test()
Dim BaseDateRng As Range
Dim SecondDateRng As Range
Dim cel As Range
Dim c As Range
Dim bDate As Date
Dim d As Long

Set BaseDateRng = Worksheets("Sheet1").Range("a2:a44")
'Range(frm1.RefBaseDate)

Set SecondDateRng = Worksheets("Sheet1").Range("c2:e811")
'Range(frm1.RefSecondDate)

For Each cel In BaseDateRng
bDate = cel.Value
With SecondDateRng.Columns(1)
Set c = .Find(bDate, LookIn:=xlFormulas)
d = c.Row - 1
If Not c Is Nothing Then
With SecondDateRng
.Rows(d).Interior.ColorIndex = 4
End With
End If
End With
Next cel
End Sub

One question tom why the "If Not IsError(res) Then" line ?
Is it nessecary ?

Dan.

"Tom Ogilvy" wrote:

Find can be problematic with dates. I prefer Match

Sub Test()
Dim BaseDateRng As Range
Dim SecondDateRng As Range
Dim cel As Range
Dim c As Range
Dim bDate As Date
Dim d As Long
Dim res as Variant

Set BaseDateRng = Worksheets("Sheet1").Range("a2:a44")
'Range(frm1.RefBaseDate)

Set SecondDateRng = Worksheets("Sheet1").Range("c2:e811")
'Range(frm1.RefSecondDate)

For Each cel In BaseDateRng
bDate = cel.Value
With SecondDateRng.Columns(1)
res = Application.Match(clng(bDate), SecondDateRng.Columns(1),0)
if not iserror(res) then
set c = SecondDateRng.Columns(1).Cells(res)
d = c.Row - 1
With SecondDateRng
.Rows(d).Interior.ColorIndex = 4
End With
End If
End With
Next cel
End Sub

--
Regards,
Tom Ogilvy

"Dan Thompson" wrote in message
...
I appreciate the Idea of conditional formating guy's, however 2 things..

1) I still don't know why the .Find method does not inclued the 1st cell
in
the specified search range. (btw only seems to be a problem when working
with
Date format)

2)I can't conditonaly format the cells do to the fact that my code is
going
to be kind of dynamic in the sense that the ranges will not be fixed the
will
be gathered from a EditRef control on a form by a user and the ranges /
worksheets / workbooks could be different every time the program is run.
Unless there is some way to have vba conditional format the ranges
selected
by the user (real time) however that seems like a bunch of extra
unessicary
steps when my code works fine already except for the one problme of the
..Find
method not including the first cell in a range as a Match found.

Dan Thompson


"Tom Ogilvy" wrote:

Why not use conditional formatting

Select column C to E and do
Format=Conditional Formatting

C1 should be the activecell in the selection


change cell value is to Formula is

in the textbox put in

=countif(A:A,$C1)0

click the format button and then pattern tab. Select a color

OK out.

--
Regards,
Tom Ogilvy

"Dan Thompson" wrote in message
...
Ok here is the situation I am having.
I have a worksheet named Sheet1 which column "A" has a bunch of dates
starting Jan 1, 2000 and going down. Column "C" Also has a bunch of
dates
starting with Jan 1, 2000 and going down. Column "D" and "E" have just
regular numerical data. Column "A" is based on 5 day week and colunm
"C"
is
based on 7 day week. and Row 1 is column hedders. What My codde should
do
when run is highlight all the dates in column "C" that match the dates
in
Column "A" and the adjacent columns which contain regular numerical
data.
My Dates in column "A" run from Jan 1, 2000 to Feb 29, 2000 (5 day
weekday
series)
My Dates in Column "C" run from Jan 1, 2000 to Mar 20, 2002 (7 day
series)

When my code is run it misses highlighting cell "C2" which should be a
matching date with cell "A2" also in colun "C" Nov 11, 2000 is
highlighted
as
a matching date but doesn't exist in column "A". ..... Anyhow here
is my
code.

Sub Test()
Dim BaseDateRng As Range
Dim SecondDateRng As Range
Dim cel As Range
Dim c As Range
Dim bDate As Date
Dim d As Long

Set BaseDateRng = Worksheets("Sheet1").Range("a2:a44")
'Range(frm1.RefBaseDate)

Set SecondDateRng = Worksheets("Sheet1").Range("c2:e811")
'Range(frm1.RefSecondDate)

For Each cel In BaseDateRng
bDate = cel.Value
With SecondDateRng.Columns(1)
Set c = .Find(bDate, LookIn:=xlFormulas)
d = c.Row - 1
If Not c Is Nothing Then
With SecondDateRng
.Rows(d).Interior.ColorIndex = 4
End With
End If
End With
Next cel
End Sub

Because in the first, part of the formula is relative, whereas sin the
second it is absolute.

The $A1:$A811 works as a test range for row 1, but in row 2, Excel updates
this to $A2:$A812, etc, and so sometimes the match will fail.

If you use

=AND($C2<"",ISNUMBER(MATCH($C2,$A$1:$A$811,0)))

that will also work

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dan Thompson" wrote in message
...
Hi Bob,
Using the Conditional Formating works fine and I figured out most of the
questions
I asked in another post regarding your Match formula so you can ignore
that
post.
However I do not understand why it is that if I use...
=AND($C2<"",ISNUMBER(MATCH($C2,$A1:$A811,0))), it does not work for
highlighting the all the appropriate matchs and yet if I use....
=AND($C2<"",ISNUMBER(MATCH($C2,$A:$A,0))) it works fine whats up with
that ?

Dan.



"Bob Phillips" wrote:

Dan,

Use conditional formatting.

Select all the cells in column C,D & E, from row 2 until the end
Goto menu FormatConditional Formatting
Change Condition 1 to Formula Is
Add the formula =AND($C2<"",ISNUMBER(MATCH($C2,$A:$A,0)))
Selct the pattern tab and choose a colour
OK out

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dan Thompson" wrote in message
...
Ok here is the situation I am having.
I have a worksheet named Sheet1 which column "A" has a bunch of dates
starting Jan 1, 2000 and going down. Column "C" Also has a bunch of
dates
starting with Jan 1, 2000 and going down. Column "D" and "E" have just
regular numerical data. Column "A" is based on 5 day week and colunm
"C"
is
based on 7 day week. and Row 1 is column hedders. What My codde should
do
when run is highlight all the dates in column "C" that match the dates
in
Column "A" and the adjacent columns which contain regular numerical
data.
My Dates in column "A" run from Jan 1, 2000 to Feb 29, 2000 (5 day
weekday
series)
My Dates in Column "C" run from Jan 1, 2000 to Mar 20, 2002 (7 day
series)

When my code is run it misses highlighting cell "C2" which should be a
matching date with cell "A2" also in colun "C" Nov 11, 2000 is
highlighted
as
a matching date but doesn't exist in column "A". ..... Anyhow here
is my
code.

Sub Test()
Dim BaseDateRng As Range
Dim SecondDateRng As Range
Dim cel As Range
Dim c As Range
Dim bDate As Date
Dim d As Long

Set BaseDateRng = Worksheets("Sheet1").Range("a2:a44")
'Range(frm1.RefBaseDate)

Set SecondDateRng = Worksheets("Sheet1").Range("c2:e811")
'Range(frm1.RefSecondDate)

For Each cel In BaseDateRng
bDate = cel.Value
With SecondDateRng.Columns(1)
Set c = .Find(bDate, LookIn:=xlFormulas)
d = c.Row - 1
If Not c Is Nothing Then
With SecondDateRng
.Rows(d).Interior.ColorIndex = 4
End With
End If
End With
Next cel
End Sub

Yes. Match returns an error if it doesn't find the date.

--
Regards,
Tom Ogilvy

"Dan Thompson" wrote in message
...
One question tom why the "If Not IsError(res) Then" line ?
Is it nessecary ?

Dan.

"Tom Ogilvy" wrote:

Find can be problematic with dates. I prefer Match

Sub Test()
Dim BaseDateRng As Range
Dim SecondDateRng As Range
Dim cel As Range
Dim c As Range
Dim bDate As Date
Dim d As Long
Dim res as Variant

Set BaseDateRng = Worksheets("Sheet1").Range("a2:a44")
'Range(frm1.RefBaseDate)

Set SecondDateRng = Worksheets("Sheet1").Range("c2:e811")
'Range(frm1.RefSecondDate)

For Each cel In BaseDateRng
bDate = cel.Value
With SecondDateRng.Columns(1)
res = Application.Match(clng(bDate), SecondDateRng.Columns(1),0)
if not iserror(res) then
set c = SecondDateRng.Columns(1).Cells(res)
d = c.Row - 1
With SecondDateRng
.Rows(d).Interior.ColorIndex = 4
End With
End If
End With
Next cel
End Sub

--
Regards,
Tom Ogilvy

"Dan Thompson" wrote in message
...
I appreciate the Idea of conditional formating guy's, however 2
things..

1) I still don't know why the .Find method does not inclued the 1st
cell
in
the specified search range. (btw only seems to be a problem when
working
with
Date format)

2)I can't conditonaly format the cells do to the fact that my code is
going
to be kind of dynamic in the sense that the ranges will not be fixed
the
will
be gathered from a EditRef control on a form by a user and the ranges
/
worksheets / workbooks could be different every time the program is
run.
Unless there is some way to have vba conditional format the ranges
selected
by the user (real time) however that seems like a bunch of extra
unessicary
steps when my code works fine already except for the one problme of
the
..Find
method not including the first cell in a range as a Match found.

Dan Thompson


"Tom Ogilvy" wrote:

Why not use conditional formatting

Select column C to E and do
Format=Conditional Formatting

C1 should be the activecell in the selection


change cell value is to Formula is

in the textbox put in

=countif(A:A,$C1)0

click the format button and then pattern tab. Select a color

OK out.

--
Regards,
Tom Ogilvy

"Dan Thompson" wrote in
message
...
Ok here is the situation I am having.
I have a worksheet named Sheet1 which column "A" has a bunch of
dates
starting Jan 1, 2000 and going down. Column "C" Also has a bunch
of
dates
starting with Jan 1, 2000 and going down. Column "D" and "E" have
just
regular numerical data. Column "A" is based on 5 day week and
colunm
"C"
is
based on 7 day week. and Row 1 is column hedders. What My codde
should
do
when run is highlight all the dates in column "C" that match the
dates
in
Column "A" and the adjacent columns which contain regular
numerical
data.
My Dates in column "A" run from Jan 1, 2000 to Feb 29, 2000 (5 day
weekday
series)
My Dates in Column "C" run from Jan 1, 2000 to Mar 20, 2002 (7 day
series)

When my code is run it misses highlighting cell "C2" which should
be a
matching date with cell "A2" also in colun "C" Nov 11, 2000 is
highlighted
as
a matching date but doesn't exist in column "A". ..... Anyhow
here
is my
code.

Sub Test()
Dim BaseDateRng As Range
Dim SecondDateRng As Range
Dim cel As Range
Dim c As Range
Dim bDate As Date
Dim d As Long

Set BaseDateRng = Worksheets("Sheet1").Range("a2:a44")
'Range(frm1.RefBaseDate)

Set SecondDateRng = Worksheets("Sheet1").Range("c2:e811")
'Range(frm1.RefSecondDate)

For Each cel In BaseDateRng
bDate = cel.Value
With SecondDateRng.Columns(1)
Set c = .Find(bDate, LookIn:=xlFormulas)
d = c.Row - 1
If Not c Is Nothing Then
With SecondDateRng
.Rows(d).Interior.ColorIndex = 4
End With
End If
End With
Next cel
End Sub