data validation

[getpras]

I have a worksheet where in i have allowed data entry in only 2 columns of the sheet. Whats required is when data is typed in any cell of these columns i should not be able to move anywere else without entering some data in the adjacent cell. For example if Col A and Col B are the two columns where data needs to be entered. Then lets say if I type something in A2 then i should be forced to type something in B2. I can leave both the cells blank but if I type in one cell then I have to type some data in the adjacent cell.

 

[Luke M]

Can't be done w/ data validation, but you could use this change event. Right click on sheet tab, view code, paste this in

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Dim SmallZone As Range
Set MyRange = Range("A:B")

If Intersect(Target, MyRange) Is Nothing Then Exit Sub

If Target.Rows.Count > 1 Then
'Multiple rows changed, can't handle this scenario
Exit Sub
End If

Set SmallZone = Cells(Target.Row, "A").Resize(1, 2)
If WorksheetFunction.CountA(SmallZone) = 1 Then
Me.ScrollArea = SmallZone.Address
Else
Me.ScrollArea = ""
End If
End Sub

[/pre]

 

[cschoenfeld]

I would like to do something that I think is similar to the above. I have a spreadsheet where, when data is entered into column B, I want data entry to be required in columns G, H and I. G, H and I happen to be data validation dropdown lists. I would also like columns G, H and I to show a fill color (as a visual they need to be filled in) when data is entered into col B. I've been trying to do it with conditional formatting and have it working but the cell shading isn't quite right and I thought it could be fixed with VBA.

Col B = Work Order Number

Col G = Time Reporting Code

Col H = Location

Col I = Task


Any help is appreciated. Thanks!