That response is a bit muddled. What was I trying to say is this: I changed
"mc" to "a" in the three places it's found. I got runtime error 91 when
opening the file. So, I also changed "mr" to "a" in the various places it's
found in the macro. Still same runtime error. I've no idea what "mr" is.
Anyway I'm sure you're tired of all this, so let's call it quits. Thank you
for all your time. Connie

"Connie Martin" wrote:

I changed mc to a everywhere mc shows up in the macro (3 places). I get
runtime error 91. So, I changed them to my column which is A. Still get
same runtime error. What is mr? Connie

"Don Guillett" wrote:

This will do it. Change mc to your column

Private Sub Workbook_Open()
mc = "i"
Sheets("sheet1").Select
With ActiveSheet.Columns(mc)
mr = .Find(CStr(Date), LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row
Cells(mr, mc).Select
Rows(mr - 1).Interior.ColorIndex = xlNone
Rows(mr).Interior.ColorIndex = 6
End With
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Connie Martin" wrote in message
...
Sorry, Don, but this macro has now removed all other rows I had coloured,
which I had said in my first posting that I didn't want the macro to
remove
any other coloured rows---only the row belonging to yesterday's date.
However, if this is getting too complicated or you have come to the end of
your patience with this, never mind. I understand. I will leave it to
Conditional Formatting. Connie

"Don Guillett" wrote:

After I showed you entirerow before, surely you could have figured it
out.
HERE

Private Sub Workbook_Open()
Sheets("sheet1").Select
With ActiveSheet.Columns("j")
.entirerow.Interior.ColorIndex = xlNone
.Find(CStr(Date), LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Select
Selection.entirerow.Interior.ColorIndex = 6
End With
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Connie Martin" wrote in message
...
Don, this morning when I opened the spreadsheet, enabled the macro,
today's
date and the entire row is highlighted, as it should be, however the
"deselection" isn't complete. Yesterday's date is no longer coloured,
but
the rest of the row is still coloured. How do I correct that? Connie

"Don Guillett" wrote:

Glad its working for you

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Connie Martin" wrote in
message
...
Don, I missed your posting where you changed the macro to highlight
the
entire row (Selection.ENTIREROW.Interior.ColorIndex = 6). I just
saw
it
now.
So, I did that , and it works the way I was looking for it to work.
So,
thank you very much! Connie

"Don Guillett" wrote:

Mine was fully tested. As I said before, send you file to me.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Stefi" wrote in message
...
To color the entire row with CF:
Select all columns of the whole used range, FormatsConditional
formattingFormula: =$A1=TODAY() and choose pink pattern.

Workbook_open macro:
Open VBA (Alt+F11)
Right click on Thisworkbook in your VBAProject
Choose View code from the local menu
Click on drop down arrow on the left hand side (next to General)
These two lines appear in the code window
Private Sub Workbook_Open()

End Sub

Insert
Columns("A:A").Find(What:=Date, After:=ActiveCell,
LookIn:=xlValues,
_
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate
between first and last line:

Private Sub Workbook_Open()
Columns("A:A").Find(What:=Date, After:=ActiveCell,
LookIn:=xlValues,
_
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate
End Sub

This is your Open macro.

Regards,
Stefi


€žConnie Martin€� ezt Ã*rta:

Don, it's working now for some reason. And I didn't make any
changes.
Anway, I won't try to figure that one out. The only other thing
is
that
only
the cell with the date is being colour pink. I would like the
entire
row.
Is that possible? Connie

"Don Guillett" wrote:

If all else fails,
If desired, send your file to my address below along
with
this
msg and
a clear explanation of what you want and before/after
examples.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Did you put the macro in the ThisWorkbook module. Also, I
think
you
must
have macros enabled FIRST.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Connie Martin"
wrote
in
message
...
Don, I changed "j" to "a" because my dates on in col. A. I
assume
that's
what that's about in the macro. I get the prompt to enable
macros.
But
nothing happens in the file. Nothing changes. Maybe I
should
stick
with
Conditional Formatting because I'm sure you're giving the
correct
information. I'm doing something incorrectly, obviously.
Connie

"Don Guillett" wrote:

There should only be ONE dot before each line in the WITH

Private Sub Workbook_Open()
Sheets("sheet1").Select
With ActiveSheet.Columns("j")
.Interior.ColorIndex = xlNone
.Find(CStr(Date), LookIn:=xlFormulas, LookAt:=xlWhole,
_
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Select
Selection.Interior.ColorIndex = 6
End With
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Connie Martin"
wrote
in
message
...
Thank you for responding. I get an error when I enable
macros.
VB
opens
up
with this pop-up: "Compile error: Syntax error" and
this
line
is
highlighted: ..Interior.ColorIndex = xlNone

But as mentioned to Stefi, maybe just having the date in
col.
A
coloured
will be sufficient. Thank you. Connie

"Don Guillett" wrote:

Place this in the ThisWorkbook module. Normally, I do
NOT
like
selections
but you want to got there.
Change cl J to suit & color 6 to suit

Private Sub Workbook_Open()
Sheets("sheet1").Select
With ActiveSheet.Columns("j")
..Interior.ColorIndex = xlNone
..Find(CStr(Date), LookIn:=xlFormulas, LookAt:=xlWhole,
_
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Select
Selection.Interior.ColorIndex = 6
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Connie Martin"

wrote
in
message
...
I'm sure this can be done. Excel can do just about
anything.
Only
wish I
could!! My spreadsheet has dates in Col. A, which
are
formatted as
Date.
Each day when I open the file, I would like Excel to
automatically
(via
a
macro, I presume) colour the row for today in pink,
remove
the
colour
pink
from last business day's row, and then end by
selecting
the
date
for