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

"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?

--
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!

"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!

--
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!

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!

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!