VBA AND Logical Operators

[Mike54]

Could someone please give me a bit of guidence, I don't understand why the second of these two dosen't work, I'm trying to meet set criteria in b5 using AND the first works if both conditions are met in two cells but bur won't on one cell?

Private Sub PTIColours_Click()
If Range("b5") > 0 And Range("c5") < 0.149 Then
Range("a5").Interior.ColorIndex = 54
End If
End Sub

But this dosen't

Private Sub PTIColours_Click()
If Range("b5") > 0 And Range("b5") < 0.149 Then
Range("a5").Interior.ColorIndex = 54
End If
End Sub


I have tried searching for a good example of how to use logical operators on Mr Excel however the search is frustrated by "Small Words" in the search field ie IF, AND, NOT ,OR are too short to search.
If anyone knows a good example, could the please direct me.

Many thanks
Mike

 

[Weaver]

For the AND to return true, both conditions have to be satisfied.

If you only need 1, try OR

 

[ajetrumpet]

works for me! something else must be going on...

 

[Mike54]

But I want both conditions to be true ie That Range B5 is both greater than 0 AND less than 14.9

Mike

 

[Weaver]

I wonder if it would work better if you put the value in a variable and ran the comparison on that?

Mind you it works as is here too.

 

[Mike54]

Okay, thanks ExcelR8R I'll give that a try.

Does anyone know where I can get an good simple example of how IF, AND, NOT & OR all work & in relation to each other. We did this in college and I can't find the examples.

 

[Richard Schollar]

Mike you currently have less than 0.149 rather than as stated in your second post "less than 14.9" - is this possibly the source of your error? Otherwise, please could you define "doesn't work" - do you get an error or just nothing happens? What value do you have in the cell B5 when you run this? The syntax is correct for your information.

 

[Mike54]

Hi Richard and thanks, I'm working in percentages therefore my 0.149 is 14.9% but you're right I was testing code with the wrong input in the test cell, Doh!

However I have 160 rows & 4 Columns of data (each is a %) so 640 cells and I need to break them down in to seven groups 0-14.9% 14.9-28.6% Etc. to 100 and the Colour Format the data entry .

So Thanks for the help, I think I may be back

Mike

 

[Richard Schollar]

Mike

Take a look at Select Case - you should be able to use this:

Select Case Range("B5").Value
  Case Is < 0
    'code here for less than zero - leave blank if you don't want anything to happen
  Case Is < 0.149
    Range("A5").Interior.ColorIndex = 3
  Case Is < 0.286
    Range("A5").Interior.ColorIndex = 5
  Case Is < 0.450
    Range("A5").Interior.ColorIndex = 7
  '...
  Case Else
    'other code for none of the above
End Select

Make sense?

 

[Mike54]

Wow that looks interesting, thanks I have play with that, looks very useful

Regards

Mike