Macro??

  • Thread starter Thread starter Connie Martin
  • Start date Start date
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
 
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
 
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
 
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
 
All you had to do was change the reference ONLY at the top where it said
mc="i". Change to mc="A"
The rest takes care of it self. If you look at mr= you will see that it is
using find to find the row where your date is
mr=.row

mr = .Find(CStr(Date), LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row


If desired, send your file to my address below
(e-mail address removed)
 
Back
Top