izet99
01-14-2015, 10:10 AM
Hi all,
I have issue with my vba code and I would really appreciate if somebody could help me to make it work. Basicly I have two vba code..
1. Auto-date stamp in column "K" if in column "J" user enter it's user ID
2. Format row "C - L" if user enter value in Column "I"
Both code seems to work when I have them execute on separate sheet, I need them to execute on the same sheet, I add them together and then I can get it to work... Anybody have any idea how would I need to combine them in order to work...
These are process step:
1. user enters disposition name (Accept, Reject, NCR etc) in column I, C-L row would be highlighted based on these value with colors assigned in vba code below.
2. in column J user enter his/her ID and soon ID is entered, current date would be assigned to column K
Anybody know how to integrate these two code/action based on sheet change.
Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'------ Stamp Date in "K" when user id entered into "J" --------
Const ColumnsToMonitor As String = "J"
Const DateColumn As String = "K"
On Error Resume Next
If Not Intersect(Target, Columns(ColumnsToMonitor)) Is Nothing Then
Intersect(Target.EntireRow, Columns(DateColumn)).Value = Now
If Intersect(Target, Columns(ColumnsToMonitor)) = "" Then
Intersect(Target.EntireRow, Columns(DateColumn)).Value = ""
End If
End If
'----- FORMAT ROW BASED ON USER INPUT IN "I" ------
If Intersect(Target, Range("I17:I2000")) Is Nothing Then Exit Sub
Select Case Cells(Target.Row, "I").Value
Case "ACCEPT"
Range(Cells(Target.Row, "C"), Cells(Target.Row, "L")).Interior.ColorIndex = 4
Case "IHR"
Range(Cells(Target.Row, "C"), Cells(Target.Row, "L")).Interior.ColorIndex = 24
Case "NCR"
Range(Cells(Target.Row, "C"), Cells(Target.Row, "L")).Interior.ColorIndex = 6
Case "OSS"
Range(Cells(Target.Row, "C"), Cells(Target.Row, "L")).Interior.ColorIndex = 45
Case "REJECT"
Range(Cells(Target.Row, "C"), Cells(Target.Row, "L")).Interior.ColorIndex = 3
Case ""
Range(Cells(Target.Row, "C"), Cells(Target.Row, "L")).Interior.ColorIndex = xlNone
Case Else
Range(Cells(Target.Row, "C"), Cells(Target.Row, "L")).Interior.ColorIndex = 2
End Select
Application.EnableEvents = True
End Sub
I have issue with my vba code and I would really appreciate if somebody could help me to make it work. Basicly I have two vba code..
1. Auto-date stamp in column "K" if in column "J" user enter it's user ID
2. Format row "C - L" if user enter value in Column "I"
Both code seems to work when I have them execute on separate sheet, I need them to execute on the same sheet, I add them together and then I can get it to work... Anybody have any idea how would I need to combine them in order to work...
These are process step:
1. user enters disposition name (Accept, Reject, NCR etc) in column I, C-L row would be highlighted based on these value with colors assigned in vba code below.
2. in column J user enter his/her ID and soon ID is entered, current date would be assigned to column K
Anybody know how to integrate these two code/action based on sheet change.
Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'------ Stamp Date in "K" when user id entered into "J" --------
Const ColumnsToMonitor As String = "J"
Const DateColumn As String = "K"
On Error Resume Next
If Not Intersect(Target, Columns(ColumnsToMonitor)) Is Nothing Then
Intersect(Target.EntireRow, Columns(DateColumn)).Value = Now
If Intersect(Target, Columns(ColumnsToMonitor)) = "" Then
Intersect(Target.EntireRow, Columns(DateColumn)).Value = ""
End If
End If
'----- FORMAT ROW BASED ON USER INPUT IN "I" ------
If Intersect(Target, Range("I17:I2000")) Is Nothing Then Exit Sub
Select Case Cells(Target.Row, "I").Value
Case "ACCEPT"
Range(Cells(Target.Row, "C"), Cells(Target.Row, "L")).Interior.ColorIndex = 4
Case "IHR"
Range(Cells(Target.Row, "C"), Cells(Target.Row, "L")).Interior.ColorIndex = 24
Case "NCR"
Range(Cells(Target.Row, "C"), Cells(Target.Row, "L")).Interior.ColorIndex = 6
Case "OSS"
Range(Cells(Target.Row, "C"), Cells(Target.Row, "L")).Interior.ColorIndex = 45
Case "REJECT"
Range(Cells(Target.Row, "C"), Cells(Target.Row, "L")).Interior.ColorIndex = 3
Case ""
Range(Cells(Target.Row, "C"), Cells(Target.Row, "L")).Interior.ColorIndex = xlNone
Case Else
Range(Cells(Target.Row, "C"), Cells(Target.Row, "L")).Interior.ColorIndex = 2
End Select
Application.EnableEvents = True
End Sub