Cell format

[one_more_cave_dweller]

Is it possible to format one cell to only allow a user to enter a value based on the contents of another cell. For example cell A2 would not let you enter a value unless cell A1 said January.

Thanks ray:

 

[P Sitaram]

Put this in the sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, [a1:a2]) Is Nothing Then Exit Sub
    If UCase([a1]) <> "JANUARY" Then [a2].ClearContents
    
End Sub

Note: The code ensures that if `January' is subsequently deleted or changed, A2 is cleared.

 

[Smitty]

In A2 you can use Data-->Validation-->Custom-->Formula-->=A1="January"

Note that you only get 3 shots per cell with Data Validation, so that may be an issue with 12 months... :wink:

Post back if that doesn't fit your bill.

HTH,

Smitty

 

[one_more_cave_dweller]

Thanks Smitty that is perfect!!!

 

[one_more_cave_dweller]

How about adding more than one value to compare to. For example January and February, how could I do that.

 

[Tazguy37]

Do the validation the same way, but change the formula to:

=OR(A1="January",A1="February")
(Add conditions as necessary. Didn't try it, but you should be able to have up to 30.)

Isn't that cool? You come up with some neat stuff, Smitty!