Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting and Macros in Excel
While I'm not new to Excel, I am new to using conditional formatting,
functions and macros within Excel. I'm working on a sheet that tracks certain data on new hires. We track this data for 3 consecutive 30 day periods. I want to create two macros that will make things easier for me to report data in this worksheet. One of the macros I would like to put in is any time I put in a new hire's info it will go ahead and create a new line with all of the appropriate formatting for each column (several of the columns will have conditional formatting). This would be similar to how a new line is created in an Access table when you input a new record. Any suggestions? The next macro deals sort of with hiding data/removing formatting. One of the specific items being tracked is a new hire's retention/turnover (meaning if they quit or not). In each 30 day period of data, there is a column that is marked "Y" or "N" for turnover (Y means they stayed, N means they quit). I need to write a macro that looks at the turnover columns in the first and second 30 days, and if there is a "N" input into either of them, then all of the conditional formatting is removed from that row, and the cells in the row (i.e. A25:U25) is marked in dark grey. I'm not sure how to go about doing that. Any help would be appreciated. Lastly, is there any books anyone could recommend on this level of work in Excel? Thanks! -- Teri Albert |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting and Macros in Excel
"tralbert" wrote in message ... While I'm not new to Excel, I am new to using conditional formatting, functions and macros within Excel. I'm working on a sheet that tracks certain data on new hires. We track this data for 3 consecutive 30 day periods. I want to create two macros that will make things easier for me to report data in this worksheet. One of the macros I would like to put in is any time I put in a new hire's info it will go ahead and create a new line with all of the appropriate formatting for each column (several of the columns will have conditional formatting). This would be similar to how a new line is created in an Access table when you input a new record. Any suggestions? What formatting, where? The next macro deals sort of with hiding data/removing formatting. One of the specific items being tracked is a new hire's retention/turnover (meaning if they quit or not). In each 30 day period of data, there is a column that is marked "Y" or "N" for turnover (Y means they stayed, N means they quit). I need to write a macro that looks at the turnover columns in the first and second 30 days, and if there is a "N" input into either of them, then all of the conditional formatting is removed from that row, and the cells in the row (i.e. A25:U25) is marked in dark grey. I'm not sure how to go about doing that. Any help would be appreciated. Where is the Y/N columns? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting and Macros in Excel
-- Teri Albert "Bob Phillips" wrote: This would be similar to how a new line is created in an Access table when you input a new record. Any suggestions? What formatting, where? The formatting in previously entered rows. Each time I input a new person I will need a new row inserted below the one I use, and would like to keep the formatting used on previous entries. Does that make any kind of sense? Where is the Y/N columns? Rows I and O respectively. Anytime an "N' is input into either one of the columns on any person I want that person's row to be shaded dark gray. Thanks for your help! |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting and Macros in Excel
"tralbert" wrote in message ... -- Teri Albert "Bob Phillips" wrote: This would be similar to how a new line is created in an Access table when you input a new record. Any suggestions? What formatting, where? The formatting in previously entered rows. Each time I input a new person I will need a new row inserted below the one I use, and would like to keep the formatting used on previous entries. Does that make any kind of sense? Yes it does, but there is no row insert event that we can piggyback onto, so we need some other cue. After you insert a new row, is there something that you always do to the same column in that new row that we could monitor for, something that you wouldn't do on a normal update of the row? Where is the Y/N columns? Rows I and O respectively. Anytime an "N' is input into either one of the columns on any person I want that person's row to be shaded dark gray. Thanks for your help! |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting and Macros in Excel
-- Teri Albert Yes it does, but there is no row insert event that we can piggyback onto, so we need some other cue. After you insert a new row, is there something that you always do to the same column in that new row that we could monitor for, something that you wouldn't do on a normal update of the row? Not that I can think of. Other than maybe hit <Enter at the end of the row I'm updating. Thanks! |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting and Macros in Excel
Perhaps something as 'simple' as a macro that after you've already inserted
the new row, then chosen a cell in the row above that has formats you need, does a copy current row, paste special | Formats in the next row down. Could be called from Tools | Macro | Macros or a button, or even a double-click in specific column in row with data in it (macro could test: has this cell got data, is next one down empty? - Yes, do the copy/paste special operation, no - ignore). Just spitballing here. "Bob Phillips" wrote: "tralbert" wrote in message ... -- Teri Albert "Bob Phillips" wrote: This would be similar to how a new line is created in an Access table when you input a new record. Any suggestions? What formatting, where? The formatting in previously entered rows. Each time I input a new person I will need a new row inserted below the one I use, and would like to keep the formatting used on previous entries. Does that make any kind of sense? Yes it does, but there is no row insert event that we can piggyback onto, so we need some other cue. After you insert a new row, is there something that you always do to the same column in that new row that we could monitor for, something that you wouldn't do on a normal update of the row? Where is the Y/N columns? Rows I and O respectively. Anytime an "N' is input into either one of the columns on any person I want that person's row to be shaded dark gray. Thanks for your help! |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting and Macros in Excel
Ok, I think I can record that easily enough.
Now to my other one. My data covers 3 separate 30 day periods, and each set has a Turnover/Retention column (columns I, O, and U respectively). If I put an N in any of those columns I am wanting a macro to run that will automatically take out any conditional formatting and grey out the relevant cells in that row. Removing the formatting and coloring the relevant cells is easy enough. I'm thinking I'm going to have to put an IF statement of some sort in the code of the macro to know what columns to look at. That's the part I'm not terribly certain how to accomplish. While I know the general gist of how to record a macro, I've never actually gone in and tinkered around with the VB before. Again, I really appreciate the help here. While I've been working on this little project I have very quickly learned that I am not only more knowledgable than I thought at Excel, but I actually know more about Excel than anyone else in my company (at least that I've talked to so far). While I may not know how to do everything, I do know quite a bit and I at least know where to go for good resources when I need help. I'm thinking more and more I really need to start looking at a certification of some sort. lol Thanks again! -- Teri Albert "JLatham" wrote: Perhaps something as 'simple' as a macro that after you've already inserted the new row, then chosen a cell in the row above that has formats you need, does a copy current row, paste special | Formats in the next row down. Could be called from Tools | Macro | Macros or a button, or even a double-click in specific column in row with data in it (macro could test: has this cell got data, is next one down empty? - Yes, do the copy/paste special operation, no - ignore). Just spitballing here. "Bob Phillips" wrote: "tralbert" wrote in message ... -- Teri Albert "Bob Phillips" wrote: This would be similar to how a new line is created in an Access table when you input a new record. Any suggestions? What formatting, where? The formatting in previously entered rows. Each time I input a new person I will need a new row inserted below the one I use, and would like to keep the formatting used on previous entries. Does that make any kind of sense? Yes it does, but there is no row insert event that we can piggyback onto, so we need some other cue. After you insert a new row, is there something that you always do to the same column in that new row that we could monitor for, something that you wouldn't do on a normal update of the row? Where is the Y/N columns? Rows I and O respectively. Anytime an "N' is input into either one of the columns on any person I want that person's row to be shaded dark gray. Thanks for your help! |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting and Macros in Excel
Why don't you take it as far as you can with point #1 then post a copy of
the workbook to a web share and we can look at 'improving' it and doing point #2. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "tralbert" wrote in message ... Ok, I think I can record that easily enough. Now to my other one. My data covers 3 separate 30 day periods, and each set has a Turnover/Retention column (columns I, O, and U respectively). If I put an N in any of those columns I am wanting a macro to run that will automatically take out any conditional formatting and grey out the relevant cells in that row. Removing the formatting and coloring the relevant cells is easy enough. I'm thinking I'm going to have to put an IF statement of some sort in the code of the macro to know what columns to look at. That's the part I'm not terribly certain how to accomplish. While I know the general gist of how to record a macro, I've never actually gone in and tinkered around with the VB before. Again, I really appreciate the help here. While I've been working on this little project I have very quickly learned that I am not only more knowledgable than I thought at Excel, but I actually know more about Excel than anyone else in my company (at least that I've talked to so far). While I may not know how to do everything, I do know quite a bit and I at least know where to go for good resources when I need help. I'm thinking more and more I really need to start looking at a certification of some sort. lol Thanks again! -- Teri Albert "JLatham" wrote: Perhaps something as 'simple' as a macro that after you've already inserted the new row, then chosen a cell in the row above that has formats you need, does a copy current row, paste special | Formats in the next row down. Could be called from Tools | Macro | Macros or a button, or even a double-click in specific column in row with data in it (macro could test: has this cell got data, is next one down empty? - Yes, do the copy/paste special operation, no - ignore). Just spitballing here. "Bob Phillips" wrote: "tralbert" wrote in message ... -- Teri Albert "Bob Phillips" wrote: This would be similar to how a new line is created in an Access table when you input a new record. Any suggestions? What formatting, where? The formatting in previously entered rows. Each time I input a new person I will need a new row inserted below the one I use, and would like to keep the formatting used on previous entries. Does that make any kind of sense? Yes it does, but there is no row insert event that we can piggyback onto, so we need some other cue. After you insert a new row, is there something that you always do to the same column in that new row that we could monitor for, something that you wouldn't do on a normal update of the row? Where is the Y/N columns? Rows I and O respectively. Anytime an "N' is input into either one of the columns on any person I want that person's row to be shaded dark gray. Thanks for your help! |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting and Macros in Excel
I think Bob has a good idea. See how things go with modifying a recorded
macro and others can help from there. There are a number of site around the net to help. http://www.mvps.org/dmcritchie/excel/getstarted.htm http://www.the-excel-advisor.com/exc...-tutorial.html http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm http://www.exceltip.com/excel_links.html Here's some code that shows first what Excel recorded when I simply used row 2's format as the basis for a 'new' row at row 3, then goes on to show how I modified it to get away from the specific rows involved with the recorded macro, and finally how I then moved things into the Worksheet_BeforeDoubleClick() event to do the process in response to a double click in column A. Sub RecordedMacro() ' ' Macro1 Macro ' Macro recorded 4/25/2008 by JLatham ' ' Rows("2:2").Select Selection.Copy Rows("3:3").Select Selection.PasteSpecial _ Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End Sub Sub ModifiedMacro() 'work on any individual row ActiveCell.EntireRow.Copy 'paste the format for the entire active row into the row below it ActiveCell.Offset(1, 0).EntireRow.PasteSpecial _ Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False 'get out of the cut/copy mode Application.CutCopyMode = False ' move into a single cell (col A) of newly formatted row ActiveCell.Select End Sub all of that put into the worksheet's _BeforeDoubleClick() event handler: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'only respond to a double-click in column A If Target.Column < 1 Then Exit Sub End If 'additional test: target cell must not be empty, and 'the cell below it must be empty If Not IsEmpty(Target) And _ IsEmpty(Target.Offset(1, 0)) Then Cancel = True ' do not do normal double-click action 'work on any individual row ActiveCell.EntireRow.Copy 'paste the format for the entire active row into the row below it ActiveCell.Offset(1, 0).EntireRow.PasteSpecial _ Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False 'get out of the cut/copy mode Application.CutCopyMode = False ' move into a single cell (col A) of newly formatted row ActiveCell.Select End If End Sub "tralbert" wrote: Ok, I think I can record that easily enough. Now to my other one. My data covers 3 separate 30 day periods, and each set has a Turnover/Retention column (columns I, O, and U respectively). If I put an N in any of those columns I am wanting a macro to run that will automatically take out any conditional formatting and grey out the relevant cells in that row. Removing the formatting and coloring the relevant cells is easy enough. I'm thinking I'm going to have to put an IF statement of some sort in the code of the macro to know what columns to look at. That's the part I'm not terribly certain how to accomplish. While I know the general gist of how to record a macro, I've never actually gone in and tinkered around with the VB before. Again, I really appreciate the help here. While I've been working on this little project I have very quickly learned that I am not only more knowledgable than I thought at Excel, but I actually know more about Excel than anyone else in my company (at least that I've talked to so far). While I may not know how to do everything, I do know quite a bit and I at least know where to go for good resources when I need help. I'm thinking more and more I really need to start looking at a certification of some sort. lol Thanks again! -- Teri Albert "JLatham" wrote: Perhaps something as 'simple' as a macro that after you've already inserted the new row, then chosen a cell in the row above that has formats you need, does a copy current row, paste special | Formats in the next row down. Could be called from Tools | Macro | Macros or a button, or even a double-click in specific column in row with data in it (macro could test: has this cell got data, is next one down empty? - Yes, do the copy/paste special operation, no - ignore). Just spitballing here. "Bob Phillips" wrote: "tralbert" wrote in message ... -- Teri Albert "Bob Phillips" wrote: This would be similar to how a new line is created in an Access table when you input a new record. Any suggestions? What formatting, where? The formatting in previously entered rows. Each time I input a new person I will need a new row inserted below the one I use, and would like to keep the formatting used on previous entries. Does that make any kind of sense? Yes it does, but there is no row insert event that we can piggyback onto, so we need some other cue. After you insert a new row, is there something that you always do to the same column in that new row that we could monitor for, something that you wouldn't do on a normal update of the row? Where is the Y/N columns? Rows I and O respectively. Anytime an "N' is input into either one of the columns on any person I want that person's row to be shaded dark gray. Thanks for your help! |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting and Macros in Excel
Unfortunately I don't have a SharePoint site I can web share the document
from. Can I email it to you? -- Teri Albert "Bob Phillips" wrote: Why don't you take it as far as you can with point #1 then post a copy of the workbook to a web share and we can look at 'improving' it and doing point #2. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "tralbert" wrote in message ... Ok, I think I can record that easily enough. Now to my other one. My data covers 3 separate 30 day periods, and each set has a Turnover/Retention column (columns I, O, and U respectively). If I put an N in any of those columns I am wanting a macro to run that will automatically take out any conditional formatting and grey out the relevant cells in that row. Removing the formatting and coloring the relevant cells is easy enough. I'm thinking I'm going to have to put an IF statement of some sort in the code of the macro to know what columns to look at. That's the part I'm not terribly certain how to accomplish. While I know the general gist of how to record a macro, I've never actually gone in and tinkered around with the VB before. Again, I really appreciate the help here. While I've been working on this little project I have very quickly learned that I am not only more knowledgable than I thought at Excel, but I actually know more about Excel than anyone else in my company (at least that I've talked to so far). While I may not know how to do everything, I do know quite a bit and I at least know where to go for good resources when I need help. I'm thinking more and more I really need to start looking at a certification of some sort. lol Thanks again! -- Teri Albert "JLatham" wrote: Perhaps something as 'simple' as a macro that after you've already inserted the new row, then chosen a cell in the row above that has formats you need, does a copy current row, paste special | Formats in the next row down. Could be called from Tools | Macro | Macros or a button, or even a double-click in specific column in row with data in it (macro could test: has this cell got data, is next one down empty? - Yes, do the copy/paste special operation, no - ignore). Just spitballing here. "Bob Phillips" wrote: "tralbert" wrote in message ... -- Teri Albert "Bob Phillips" wrote: This would be similar to how a new line is created in an Access table when you input a new record. Any suggestions? What formatting, where? The formatting in previously entered rows. Each time I input a new person I will need a new row inserted below the one I use, and would like to keep the formatting used on previous entries. Does that make any kind of sense? Yes it does, but there is no row insert event that we can piggyback onto, so we need some other cue. After you insert a new row, is there something that you always do to the same column in that new row that we could monitor for, something that you wouldn't do on a normal update of the row? Where is the Y/N columns? Rows I and O respectively. Anytime an "N' is input into either one of the columns on any person I want that person's row to be shaded dark gray. Thanks for your help! |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting and Macros in Excel
There are plenty of free web hosting sites, they will host a file for a
certain time. Post it there, others can see it then. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... Why don't you take it as far as you can with point #1 then post a copy of the workbook to a web share and we can look at 'improving' it and doing point #2. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "tralbert" wrote in message ... Ok, I think I can record that easily enough. Now to my other one. My data covers 3 separate 30 day periods, and each set has a Turnover/Retention column (columns I, O, and U respectively). If I put an N in any of those columns I am wanting a macro to run that will automatically take out any conditional formatting and grey out the relevant cells in that row. Removing the formatting and coloring the relevant cells is easy enough. I'm thinking I'm going to have to put an IF statement of some sort in the code of the macro to know what columns to look at. That's the part I'm not terribly certain how to accomplish. While I know the general gist of how to record a macro, I've never actually gone in and tinkered around with the VB before. Again, I really appreciate the help here. While I've been working on this little project I have very quickly learned that I am not only more knowledgable than I thought at Excel, but I actually know more about Excel than anyone else in my company (at least that I've talked to so far). While I may not know how to do everything, I do know quite a bit and I at least know where to go for good resources when I need help. I'm thinking more and more I really need to start looking at a certification of some sort. lol Thanks again! -- Teri Albert "JLatham" wrote: Perhaps something as 'simple' as a macro that after you've already inserted the new row, then chosen a cell in the row above that has formats you need, does a copy current row, paste special | Formats in the next row down. Could be called from Tools | Macro | Macros or a button, or even a double-click in specific column in row with data in it (macro could test: has this cell got data, is next one down empty? - Yes, do the copy/paste special operation, no - ignore). Just spitballing here. "Bob Phillips" wrote: "tralbert" wrote in message ... -- Teri Albert "Bob Phillips" wrote: This would be similar to how a new line is created in an Access table when you input a new record. Any suggestions? What formatting, where? The formatting in previously entered rows. Each time I input a new person I will need a new row inserted below the one I use, and would like to keep the formatting used on previous entries. Does that make any kind of sense? Yes it does, but there is no row insert event that we can piggyback onto, so we need some other cue. After you insert a new row, is there something that you always do to the same column in that new row that we could monitor for, something that you wouldn't do on a normal update of the row? Where is the Y/N columns? Rows I and O respectively. Anytime an "N' is input into either one of the columns on any person I want that person's row to be shaded dark gray. Thanks for your help! |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting and Macros in Excel
Actually, I just found out that I have an internal source that can help me,
and it's preferred I use that source when it comes to writing macros. So I'm all set. Thanks again for all your help! -- Teri Albert "JLatham" wrote: I think Bob has a good idea. See how things go with modifying a recorded macro and others can help from there. There are a number of site around the net to help. http://www.mvps.org/dmcritchie/excel/getstarted.htm http://www.the-excel-advisor.com/exc...-tutorial.html http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm http://www.exceltip.com/excel_links.html Here's some code that shows first what Excel recorded when I simply used row 2's format as the basis for a 'new' row at row 3, then goes on to show how I modified it to get away from the specific rows involved with the recorded macro, and finally how I then moved things into the Worksheet_BeforeDoubleClick() event to do the process in response to a double click in column A. Sub RecordedMacro() ' ' Macro1 Macro ' Macro recorded 4/25/2008 by JLatham ' ' Rows("2:2").Select Selection.Copy Rows("3:3").Select Selection.PasteSpecial _ Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End Sub Sub ModifiedMacro() 'work on any individual row ActiveCell.EntireRow.Copy 'paste the format for the entire active row into the row below it ActiveCell.Offset(1, 0).EntireRow.PasteSpecial _ Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False 'get out of the cut/copy mode Application.CutCopyMode = False ' move into a single cell (col A) of newly formatted row ActiveCell.Select End Sub all of that put into the worksheet's _BeforeDoubleClick() event handler: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'only respond to a double-click in column A If Target.Column < 1 Then Exit Sub End If 'additional test: target cell must not be empty, and 'the cell below it must be empty If Not IsEmpty(Target) And _ IsEmpty(Target.Offset(1, 0)) Then Cancel = True ' do not do normal double-click action 'work on any individual row ActiveCell.EntireRow.Copy 'paste the format for the entire active row into the row below it ActiveCell.Offset(1, 0).EntireRow.PasteSpecial _ Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False 'get out of the cut/copy mode Application.CutCopyMode = False ' move into a single cell (col A) of newly formatted row ActiveCell.Select End If End Sub "tralbert" wrote: Ok, I think I can record that easily enough. Now to my other one. My data covers 3 separate 30 day periods, and each set has a Turnover/Retention column (columns I, O, and U respectively). If I put an N in any of those columns I am wanting a macro to run that will automatically take out any conditional formatting and grey out the relevant cells in that row. Removing the formatting and coloring the relevant cells is easy enough. I'm thinking I'm going to have to put an IF statement of some sort in the code of the macro to know what columns to look at. That's the part I'm not terribly certain how to accomplish. While I know the general gist of how to record a macro, I've never actually gone in and tinkered around with the VB before. Again, I really appreciate the help here. While I've been working on this little project I have very quickly learned that I am not only more knowledgable than I thought at Excel, but I actually know more about Excel than anyone else in my company (at least that I've talked to so far). While I may not know how to do everything, I do know quite a bit and I at least know where to go for good resources when I need help. I'm thinking more and more I really need to start looking at a certification of some sort. lol Thanks again! -- Teri Albert "JLatham" wrote: Perhaps something as 'simple' as a macro that after you've already inserted the new row, then chosen a cell in the row above that has formats you need, does a copy current row, paste special | Formats in the next row down. Could be called from Tools | Macro | Macros or a button, or even a double-click in specific column in row with data in it (macro could test: has this cell got data, is next one down empty? - Yes, do the copy/paste special operation, no - ignore). Just spitballing here. "Bob Phillips" wrote: "tralbert" wrote in message ... -- Teri Albert "Bob Phillips" wrote: This would be similar to how a new line is created in an Access table when you input a new record. Any suggestions? What formatting, where? The formatting in previously entered rows. Each time I input a new person I will need a new row inserted below the one I use, and would like to keep the formatting used on previous entries. Does that make any kind of sense? Yes it does, but there is no row insert event that we can piggyback onto, so we need some other cue. After you insert a new row, is there something that you always do to the same column in that new row that we could monitor for, something that you wouldn't do on a normal update of the row? Where is the Y/N columns? Rows I and O respectively. Anytime an "N' is input into either one of the columns on any person I want that person's row to be shaded dark gray. Thanks for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with macros and formatting templates in Excel | Excel Worksheet Functions | |||
Conditional Formatting for Excel XP. | Excel Worksheet Functions | |||
conditional formatting in excel | Excel Worksheet Functions | |||
Conditional Formatting in Excel | Excel Worksheet Functions | |||
Conditional formatting based on decision to enable/disable macros? | Excel Discussion (Misc queries) |