Checking a value in a cell above the selected cell

[calmena]

Does anyone know the code for checking a value in a cell above the cell that is currently selected? I will try to explain.

I have a range (B17:B43) and when the user selects a cell in that range, the code will look in the cell directly above the one that is selected, and check if it contains the word "logging". If it does then it checks to see if a cell in the same row but a different column is populated. If it isn't, then a msgbox appears and will populate that cell with the users inputs.

I will try to explain further with my comments. this is what i have so far.

Please Login or Register  to view this content.

[Colin Legg]

Hi,

I haven't checked the logic of your code and you didn't mention what problem(s) you are having with it. However, I did immediately notice that you have at least one (unfortunate) typo:

Please Login or Register  to view this content.

If you declare you variables and enforce declaration with Option Explicit, then typos such as this one will be picked up for you.

[calmena]

Thanks for catching that, however I have no idea how to declare variables with option explicit as you mentioned.

The problem that I am having is that the two lines of code that are commented "this isn't working" are not working. what is happeing is that when I comment out those two lines of code, (PrevRow =, and PrevTarget =), the rest of the code works well, but its checking the current target cell for the text "logging", not the cell above the currently selected cell. So I need to point it to look at the cell above the selected target cell. This is kind of hard to explain, I hope that helps.

[watersev]

hi, calmena,

try this out:

Please Login or Register  to view this content.

Colin gave you very useful advice on Option explicit usage. It requires all the variable to be declared as in example above.

VB help for Option Explicit:

Syntax

Option Explicit

Remarks

If used, the Option Explicit statement must appear in a module before any procedures.

When Option Explicit appears in a module, you must explicitly declare all variables using the Dim, Private, Public, ReDim, or Static statements. If you attempt to use an undeclared variable name, an error occurs at compile time.

If you don't use the Option Explicit statement, all undeclared variables are of Variant type unless the default type is otherwise specified with a Deftype statement.

Note Use Option Explicit to avoid incorrectly typing the name of an existing variable or to avoid confusion in code where the scope of the variable is not clear.

[calmena]

Thank you so much! its works perfect!

Cheers!