LinLin

The following formula in any cell will produce "Value not valid" if the
value in A1 is present in the range B2:B9. A blank cell will be produced if
not.

=IF(COUNTIF(B2:B9,A1)0,"Value not valid","")



As an example of what VBA can do, the following macro will produce a message
box on the screen, saying "The entry is invalid." if the value in A1 is in
Column B anywhere from B2 down. The same macro can easily add/remove cell
color. As written, this macro will clear A1 if it is a duplicate. HTH
Otto

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngColB As Range
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
If IsEmpty(Target.Value) Then Exit Sub
Set RngColB = Range("B2", Range("B" & Rows.Count).End(xlUp))
If RngColB.Find(What:=Target.Value, LookAt:=xlWhole) Is Nothing Then
MsgBox "The entry is invalid.", 16, "Invalid Entry"
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End If
End Sub
"LinLin" wrote in message
...
Actually, my last post makes it sound too easy.

I am making a form to help people set up an account number in a chart of
accounts.
The cell A1 tells them if the number they select is already used. So in
conditional formatting I've set up a "countif" formula which looks at the
existing list of account numbers and returns a RED cell if it matches.

(I thought I'd better add this because this is why the IF statement is
complicated, the list of numbers to compare to is too long, but if I could
get it to compare to the Colour Red, my life would be simplier!)

thanks!


"Otto Moehrbach" wrote:

LinLin
You cannot write an IF statement formula that looks at the color of
the
cell. VBA can do that, but not a formula. Perhaps you can work with the
condition that makes that cell colored? Come back if you want to try a
VBA
solution. Provide more detail about what you have and what you want to
do.
HTH Otto
"LinLin" wrote in message
...
Hi Everyone

I'm not sure how to write an IF statement.

If Cell A1 is shaded RED, I want cell B1 to to say OK
If A1 is not red, then no message.

The IF statement is obviously in cell B1.

As far as I can see, conditional formatting will not work, because I
want
a
message in B1, not a format.

Any ideas?

many thanks