Updating Cells based on condition of other cells

Having a little trouble finding help specific to this question. My VBA knowledge is a bit limited; up to this point, I've basically just been editing recorded macros.

Here is what I'm trying to do:

A workbook containing hundreds of records (one row per record) will be accessed by multiple users - not simultaneously, as it will not be a shared workbook. I want each user to be able to check records out so nobody else can edit them.

I have one column, column AE, which populates with an "X" when the record is displayed on a separate tab. Column AF has "In" or "Out" for each record, indicating whether or not the record will be accessible by others.

I need to figure out how to write a macro so that the cell in column AF changes to "Out" for each row that contains the "X" in column AE. Given an example, I can probably figure out how to have these records checked back in at a later time.

Thanks for the help in advance!

Originally Posted by davegugg

Can you just use a formula?:

Please Login or Register  to view this content.

Copy down column AF.

Not in this case, because the "X" populates only when the records are displayed on another tab, which is where they are copied from. When the next user opens the workbook, that separate tab is blank and no X's will populate until they choose which records they want to check out. However, column AF has to remain at "Out", even though the X's are now gone.

Originally Posted by MarvinP

Hi fooledbylogic and welcome to the forum,

Find the attached that may do what you request.

Hi Marvin, and thanks for the help! I will test the code as soon as I get back to the office. You guys will like me here - I tend to ask some questions that are probably pretty simple to the advanced user!

Ok, so I tested the formula, and it successfully changes the correct records from "In" to "Out". However, and this is likely a result of an omission I made from my initial post, the "Else" function changes all the records that are not on the list back to "In", but I need it to keep all the other cells at the values they were at before the macro was run. This is because some records will be checked out, and will need to remain checked out, even if the current user is not the one doing so.

Hoping that makes sense...