Forced Entry based on value

[RobertCotton]

I've searched the board but didn't find anything I thought would wokr for my particular problem.

I have a mileage tracking spreadsheet where I use cell validation in column C for the user to select either "Business" or "Personal". What I would like to do is force the user to make a numeric entry in column F if "Personal" is selected in Column C.

Does anyone have any thoughts on how to accomplish this?

Thanks

 

[rdw72777]

What do you mean by force them? You could have a pop-up box come up, you could have something appear in column F, etc, but I'm not sure how you'd "force" anyone to do anything really.

 

[RobertCotton]

I suppose force is the wrong choice of words. Although it would be nice if I could do so (i.e. acitvate the correponding cell in column F and not allow them to leave it unless a numeric value is enterd) Perhaps a prompt or message box is a better solution. Something like "you have selected Personal, Please enter your personal mileage in coulmn F".

Does this help?

 

[rdw72777]

You'll have to paste this in the module for the worksheet in question (i.e. if its sheet1, paste it in the module for sheet1.

Private Sub Worksheet_Change(ByVal Target As Range)

If Left(Target.Address, 2) = "$c" Then Exit Sub
If Target.Value = "Personal" Then
Range("$f" & Right(Target.Address, Len(Target.Address) - 2)).Select
Range("$f" & Right(Target.Address, Len(Target.Address) - 2)).Interior.ColorIndex = 6
MsgBox "You have selected Personal, please enter your personal mileage in coulmn F"

End If
End Sub

 

[RobertCotton]

Excellent solution. Thank you very much.