Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro??
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro??
1. Select column A, FormatsConditional formattingCell 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: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro??
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 "Stefi" wrote: 1. Select column A, FormatsConditional formattingCell 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: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro??
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 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro??
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 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro??
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 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro??
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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help to update macro to office 2007 macro enabled workbook | Excel Discussion (Misc queries) | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) |