Formulas in Conditional Formatting

[broncos347]

I have a spreadsheet with a number of columns and I am trying to us a formula in conditional formatting to turn a cell a different colour should certain criteria be met.

Basically, column B has a number and column E has a date. What I am trying to do to change the cell in B a different colour should the number in B be higher than 50, but only if there is no date in column E.

I've tried the following formula but I can't get it to work:

-IF(AND(B1>49,E1=0,""))

Can anyone help?

 

[Peter_SSs]

Try just this:
=AND(B1>49,E1="")

 

[barry houdini]

You don't need the If statement, try

=AND(B1>49,E1="")

edit...or just to give you an alternative to Peter's...

=(B1>49)*(E1="")

 

[andshep]

or here is a bit of code

Sub colourB()

For i = 1 To 65536
    If WorksheetFunction.And(Cells(i, 2) > 49, Cells(i, 5) <> 0) Then Cells(i, 2).Interior.ColorIndex = 3
Next i

End Sub

[/code]

 

[broncos347]

Excellent, thanks all.