Macro??

[Connie Martin]

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 today.
So, today it would've removed the colour from row 5, which was Friday
(weekends have been excluded from this spreadsheet), and then would've
coloured row 6 pink and then select A6. I would like this macro to run when
the spreadsheet is opened. Can this be done? Please note: there are other
coloured cells and rows in this spreadsheet that I don't want the macro to
mess with. Thank you. Connie

 

[Stefi]

1. Select column A, Formats>Conditional formatting>Cell value: equals to
=TODAY() and choose pink pattern.
2. Insert this line in Workbook_Open event macro:

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

Regards,
Stefi


„Connie Martin†ezt írta:

 

[Don Guillett]

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

 

[Connie Martin]

Thank you for responding. The conditional formatting works, but the macro
doesn't appear to work. When I open the file after saving all the changes, I
don't get the "Disable/Enable Macro" pop-up that I normally get in files that
have macros, and nothing has changed in the file. But perhaps just
colouring the date in col. A is sufficient without bothering to colour the
whole row. Connie

 

[Connie Martin]

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]

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

 

[Connie Martin]

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]

Did you put the macro in the ThisWorkbook module. Also, I think you must
have macros enabled FIRST.

 

[Don Guillett]

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.

 

[Connie Martin]

I'll check into this further, Don. But I'm sure the answer is yes. I'm
working on a big report right now and might not get back to this until
tomorrow. But I will post back. Connie

 

[Connie Martin]

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]

Selection.ENTIREROW.Interior.ColorIndex = 6

 

[Stefi]

To color the entire row with CF:
Select all columns of the whole used range, Formats>Conditional
formatting>Formula: =$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:

 

[Connie Martin]

This highlights A1 pink and that's it, that is, if I did it correctly. I
appreciate everyone's help, but this isn't working. Too many posts. I will
stay with conditional formatting to colour the date cell only for today's
date. Thank you for all your trouble. Connie

 

[Don Guillett]

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

 

[Connie Martin]

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]

Glad its working for you

 

[Connie Martin]

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]

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

 

[Connie Martin]

Thank you, Don, for your patience. I'm sorry, but I cannot read these macros
very well, nor can I write them. They are Greek to me---almost. I record
macros in the spreadsheet easily enough, but can't write them in VB. I've
appreciated all the help that is available on this website from people who
love to help others. It's an invaluable tool in my job. I try to do the
tasks myself before posting a question. I love Excel and love to make it
work for me. I've done many challenging things in it on my own, but
sometimes, what I want to do is far beyond my knowledge. I would not have
known that the line where you put the deselection for yesterday's date, would
be the line to change. I have no idea what xlNone means. I would not have
known. Once again, thank you. Connie