R
Ron
Hello all,
I’m in need of VBA assistance that may be a little over my head. I’ve
prowled the archives and attempted on my own to come up with a
solution but I’m getting nowhere. Appreciate any assistance.
I’m developing a macro that will validate a spreadsheet that must meet
PeopleSoft standards prior to uploading to PeopleSoft. One issue
remaining open is: Account number in column K and department ID in
column N. Should the account number in column K be within the range
of 100000 – 330000 and accounts 939001 and 939002 then corresponding
cell in column N or department ID must be blank. I’ve addressed this
range with If c.Offset(0, -3) <= 330000 Then c.ClearContents End If.
Please see complete Macro below.
Should the column K account numbers range from 400000 -799999 and
equal or greater than 940000 a department ID must be entered in column
N. I’m not sure how to alert the user that a dept ID must be
entered.
I think I've covered the first paragraph with the code below but, I'm
not sure how to proceede with the second paragraph. Would I use a
Msgbox or ??? Appreciate your assistance with this code. Thank you,
Ron
Sub ValDataN()
Dim c As Range
ActiveSheet.Unprotect
Range("E12").Select
Range(Selection, Selection.End(xlDown)).Offset(0, 9).Select
For Each c In Selection
If Len(c.Value) > 6 Then
c.Interior.ColorIndex = 3
End If
If Len(c.Value) < 6 Then
c.Interior.ColorIndex = 3
End If
If Len(c.Value) = 0 Then
c.Interior.ColorIndex = 0
End If
If Len(c.Value) = 6 Then
c.Interior.ColorIndex = 0
End If
If c.Offset(0, -3) <= 330000 Then
c.ClearContents
End If
If c.Offset(0, -3) = 939001 Then
c.ClearContents
End If
If c.Offset(0, -3) = 939002 Then
c.ClearContents
End If
Next c
End Sub
I’m in need of VBA assistance that may be a little over my head. I’ve
prowled the archives and attempted on my own to come up with a
solution but I’m getting nowhere. Appreciate any assistance.
I’m developing a macro that will validate a spreadsheet that must meet
PeopleSoft standards prior to uploading to PeopleSoft. One issue
remaining open is: Account number in column K and department ID in
column N. Should the account number in column K be within the range
of 100000 – 330000 and accounts 939001 and 939002 then corresponding
cell in column N or department ID must be blank. I’ve addressed this
range with If c.Offset(0, -3) <= 330000 Then c.ClearContents End If.
Please see complete Macro below.
Should the column K account numbers range from 400000 -799999 and
equal or greater than 940000 a department ID must be entered in column
N. I’m not sure how to alert the user that a dept ID must be
entered.
I think I've covered the first paragraph with the code below but, I'm
not sure how to proceede with the second paragraph. Would I use a
Msgbox or ??? Appreciate your assistance with this code. Thank you,
Ron
Sub ValDataN()
Dim c As Range
ActiveSheet.Unprotect
Range("E12").Select
Range(Selection, Selection.End(xlDown)).Offset(0, 9).Select
For Each c In Selection
If Len(c.Value) > 6 Then
c.Interior.ColorIndex = 3
End If
If Len(c.Value) < 6 Then
c.Interior.ColorIndex = 3
End If
If Len(c.Value) = 0 Then
c.Interior.ColorIndex = 0
End If
If Len(c.Value) = 6 Then
c.Interior.ColorIndex = 0
End If
If c.Offset(0, -3) <= 330000 Then
c.ClearContents
End If
If c.Offset(0, -3) = 939001 Then
c.ClearContents
End If
If c.Offset(0, -3) = 939002 Then
c.ClearContents
End If
Next c
End Sub