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

 

[tusharm]

If you have Excel 2007, check out Excel's conditional formatting capability. It's always better to let Excel do something that it can do natively rather than writing code to duplicate that functionality.

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

 

[Mike54]

Thanks for that yes I have 2007 at home but the office is still on 97! What can you do?