trying to make default setting

[niyrho]

I'm making an inspection report. I want some feilds on it to default to a certain value. Its layed otu like:

column B is Device type
column C is Location
column H is zone
column I is Pass/Fail
column J is hight

I need a code that will, if column B is anything other than blank then column I in the same row will default to "Pass" and column J in the same row will default to "No". I still want the option of changing them after they go to their default values. This is just to save time.

I don't know if it will affect it, but I'm using data validation for a dropdown list in columns B,I and J.

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B:B" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

If .Value <> "" Then

.Cells(.Row, "I").Value = "Pass"
.Cells(.Row, "J").Value = "No"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[niyrho]

That code almost works perfect. It isn't putting the pass/no in the same rows. It looks like it kinda doing it at random.

[Bob Phillips]

Apologies mate, I'm so cocky, I didn't even test it :doh:



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B:B" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

If .Value <> "" Then

Me.Cells(.Row, "I").Value = "Pass"
Me.Cells(.Row, "J").Value = "No"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

[niyrho]

Ha ha ha, thats perfect. Thanks man.