How to check adjacent cell for value and display error message for certain conditions

[ScotyB]

Hi all,

as roster coordinator for my workplace, I usually handle all the allocation of overtime when it arises. I was on vacation just recently, however, and a couple of other supervisors incorrectly allocated some overtime. To get around that human error, I've been trying to code something that would check cell values and throw up a message box if certain conditions were met. I've been trying a sheet change event using offset but without much luck so I'm hoping someone can point me in the right direction. Basically I'm trying to achieve this...

Range of cells is A4 to AA42

If a user enters a value of "L" or "OL" into the sheet, it should check the cell immediately to the left, and if it finds a value of "N" or "ON', throw up the message box. Conversely, if a user enters "N' or "ON' into a cell, it should check the cell immediately to the right for a value of "L" or "OL", and if found, throw up a message box.

Thanks in advance for any suggestions,

AJ

[Trebor76]

If a user enters a value of "L" or "OL" into the sheet, it should check the cell immediately to the left

Hi ScotyB,

If we're in cell A4 you can't check to the immediate left so what's the rule there?

Robert

[bakerman2]

How about this for starters ?

Please Login or Register  to view this content.

[ScotyB]

Lol, my bad, Trebor - it should have been B4 - I've just finished a night shift and was tired...

Good pickup - ta

AJ

[Trebor76]

Hi AJ,

Seems like bakerman2 has already come up with a nifty solution

Robert

[ScotyB]

Thanks, bakerman, have just tried that and it works a treat.

Much appreciated,

AJ

[ScotyB]

Indeed, trebor, but thanks for taking the time to have a look - appreciated.

AJ

[bakerman2]

You're welcome and thanks for rep+.