VB question

[pickwa]

Hi Guys,
Im really clueless when it comes to VB, so i was hoping someone could point me in the rite direction
The following code was written to make a status box appear in column I.

How would i make it so that it is in column B?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Column = 10 Then
If Target.Value > 0 Then Target.Offset(0, -1).Value = "processing"
End If
If Target.Column = 11 Then
If Target.Value = "No" Then Target.Offset(0, -2).Value = "Send 1st Letter"
If Target.Value = "Yes" Then Target.Offset(0, -2).Value = "Case Closed"
End If
If Target.Column = 12 Then
If Target.Value <> "" Then Target.Offset(0, -3).Value = "1st letter sent"
If Target.Value > Date - 30 Then Target.Offset(0, -3).Value = "send Charge Letter"
End If
If Target.Column = 13 Then
If Target.Value <> "" Then Target.Offset(0, -4).Value = "Charge Letter Sent"
If Target.Value > Date - 30 Then Target.Offset(0, -4).Value = "Send Surcharge Leter"
End If
If Target.Column = 14 Then
If Target.Value > "0" Then Target.Offset(0, -5).Value = "Surcharge Letter Sent"
If Target.Value > Date - 30 Then Target.Offset(0, -5).Value = "Pass On"
End If
If Target.Column = 15 Then
If Target.Value >= "0" Then Target.Offset(0, -6).Value = "Case Closed"
End If
If Target.Column = 17 Then
If Target.Value = "YES" Then Target.Offset(0, -6).Value = "Paid"
End If
End Sub


Cheers

Nick

 

[Steeviee]

The bit where you see OFFSET is the clue
When Target column = 10, the offset is (0,-1) - giving 9 (10-1) or column I. To get to column B, you need that offset to give a result of 2 (10-8):

If Target.Column = 10 Then
If Target.Value > 0 Then Target.Offset(0, -8).Value = "processing"
End If

You can repeat this logic throughout the code.

 

[pickwa]

oh rite i get it!

thanks ill give it a go.

i do have one other question though.

What does the first bit of the code do?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub

Thanks again

Nick

 

[Steeviee]

The first line sets off the macro when you change a value in the spreadsheet, the second line stops this happening if you highlight more than one cell - that would mess up the macro as it needs just one cell.