• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to check if the cell does not have pre-defined value(s)

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,

I am trying to write below mentioned line but not able to do so.
Can someone help me in this please.

First let me explain what I am able to complete.

If Range A1 value does not equal to High then highlight the cell.

I am not able to write condition using OR operator.
For example I want to write,

If Range A1 value does not equal to High or Low then highlight the cell.

If ABCSheet.Cells(i, MCol(DEFSheet.Range("a1")).Column).Value <> "High" Or "Low" Then

I am getting bug in the above mentioned line.
 
Hi Sachin ,

In VBA , if you have to check whether a variable Var1 has a value Value1 or another value Value2 , the statement would be :

If ((Var1 = Value1) Or (Var1 = Value2)) Then

Narayan
 
Hi Narayan Sir,

Thanks for the help.

Sorry to distrub you again. I am not getting correct result with below mentioned code.

Could you please help if possible.


Code: 
Sub text()
    If VlookUpSheet.Range("f1").Value <> "Yes" Or VlookUpSheet.Range("a1").Value <> "No" Then
        VlookUpSheet.Range("f1").Interior.Color = 65535
    End If
End Sub
 
Hi Sachin ,

That depends on what you mean by correct result ; what do you want to do ?

There is no syntax error in the code you have posted ; should you be using the AND operator instead of the OR operator ?

Narayan
 
Hi Narayan Sir,


Yes, you are correct. I should have used AND operator instead of OR.

This is working now, thanks for the help.


I was trying something similar as mentioned below.


If F1 value = Yes then do nothing

If F1 Value = No then do nothing


IF F1 Value is neither Yes nor No then highlight.

Somehow I was feeling that I should have used OR operator and that is where I made mistake(s) I guess.



Have a nice day ahead.
 
Hi,

Would like to share one observation of mine with you.


If F1 = Yes, F1 = No, F1 = blank then highlight else do nothing.

In this case I need to use OR operator


But when F1 = Yes, F1 = No then do nothing else highlight.

Then I need to use AND operator.


I am yet to understand logic for this but sharing for Forum’s view.
 

Hi !

Nope, it's again an OR operator ‼

Why ? Simple logic !

If my pen writes in black AND my pen writes in blue Then … never be True
'cause my pen writes in only one colour !
Like If Yoda is green And Yoda is grey …
tete-yoda-3d.gif

​
 
Hi Marc,


Sorry for confusing you, I will provide both the examples with the performing code shortly.


Have a nice day ahead.
 
Hi Sachin ,

Let us start with your statements :

If F1 value = Yes then do nothing

If F1 Value = No then do nothing

IF F1 Value is neither Yes nor No then highlight.

The starting point in coding is to decide which is the significant action ; in your case , you have two routes , one where you do nothing , and the other where you highlight.

It is clear that the significant route is the highlight route.

Thus your pseudo-code will be :

If (conditions satisfied) Then highlight

What are these conditions ?

You have mentioned :

If F1 is neither Yes nor No.

The logical way to represent this statement is :

If Not ((F1 = "Yes") Or (F1 = "No"))

The condition will be satisfied when F1 has value which is neither Yes nor No , a value such as Maybe.

If you try out this value in the above statement , if F1 contains Maybe , it is obviously not equal to "Yes" ; it is also not equal to "No" ; thus each individual test evaluates to False , the OR also evaluates to False , and the negation of this evaluates to True.

Please note that Maybe in this context is just a text value , and not used in the sense we normally use it in English , where Maybe means Yes or No.

Narayan
 
Hi Narayan Sir,


Thanks a lot for you detailed explanation (and valuable time).


I will study your note in detail once I reach home. Good night.
 
You must log in or register to reply here.
Back
Top