Auto filing and condtional formatting cells based on cell value.

[CJPHX]

Afternoon all,

I am currently working on a spreadsheet to track training classes. I have one sheet that has two columns. Column A is the type of Class and Column B is the class start date.
I then have two sheets one of each type of class(as there is only two).

Every time a new input is entered one Sheet 1(Classes) I need it to go to the sheet that matches the class and find the column that matches the class start date. Row 1 has each week of the month listed ie: 1/3, 1/10, 1/17. Then move down and input the class start date and highlight 5 columns over one color to mark class room training, then 8 columns over form there to mark Job Training Time. I would like the corresponding sheet to update as classes are entered without the need for actually clicking to run the macro.

I am attaching an example sheet of what I would like it to look like.

[Leith Ross]

Hello CJPHX,

The attached workbook will color the cells on the correct worksheet for the date indicated. If the date is not found then a message is displayed indicating this and the macro exits. Both cells in columns "A" and "B" must be filled in before the macro will update the worksheet named in column "A".

There are two macros. The main macro which does the formatting and the other which is triggered when the cells have been changed.

Main Macro

Please Login or Register  to view this content.

Worksheet Change Event Macro

Please Login or Register  to view this content.

[CJPHX]

Leith this works great. I do only have one question. How does it determine the output? If there is a few monhts between classes I noticed its output is offset quite a bit. How difficult would it be to make it find the next available row and go there?

[Leith Ross]

Hello CJPHX,

Can you post an example of the problem?

[CJPHX]

I am enclosing the sample worksheet. With two dates shwoing the differnece on the ASG tab.

[CJPHX]

sorry Leith, what I would like it do is in this instance the next class would show up in the next row instead of all the way down there.

[Leith Ross]

Hello CJPHX,

The macro has been changed to insert the next entry below the last one. Here is the updated macro that has been added to the attached workbook.

Please Login or Register  to view this content.

[CJPHX]

Thank Leith for all of your help. Sorry for the delayed response. I had left for the day. Thank you again for all of your help.