Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > It works but I need better code...
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default It works but I need better code...
The following code works fine. I believe I can use a do loop to make
the code more efficient. Can someone help? I appreciate it.

Private Sub Worksheet_Change(ByVal Target As Range)
getReportingCode

On Error GoTo ErrorHandler

ActiveSheet.Unprotect
Dim mnb As Integer

Dim asd, asd2, colb, cole, colf, colg, colh, coli2l, cole2l As
Integer
Application.EnableEvents = False


If Not Intersect(Target, Range("B7:L35")) Is Nothing Then

colb = Application.CountA(Worksheets("Employee").Range("b " &
Target.Row))
cole = Application.CountA(Worksheets("Employee").Range("e " &
Target.Row))
colf = Application.CountA(Worksheets("Employee").Range("f " &
Target.Row))
colg = Application.CountA(Worksheets("Employee").Range("g " &
Target.Row))
colh = Application.CountA(Worksheets("Employee").Range("h " &
Target.Row))
coli2l = Application.CountA(Worksheets("Employee").Range("i " &
Target.Row & ":" & "l" & Target.Row))
cole2l = Application.CountA(Worksheets("Employee").Range("i " &
Target.Row & ":" & "l" & Target.Row))


If (colb 0) Then
If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (cole 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (colf 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (colg 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (colh 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (coli2l 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If


End If



End If


Application.EnableEvents = True
Application.ScreenUpdating = True

ActiveSheet.Protect

Exit Sub
ErrorHandler:
Application.EnableEvents = True
' MsgBox Err.Number & " " & Err.Description
Resume Next
End Sub


Thanks.

Faye

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default It works but I need better code...
Is this better????



Private Sub Worksheet_Change(ByVal Target As Range)
getReportingCode

On Error GoTo ErrorHandler

ActiveSheet.Unprotect
Dim mnb As Integer

Application.EnableEvents = False


If Not Intersect(Target, Range("B7:L35")) Is Nothing Then


If Target.Column = 2 Or _
(Target.Column = 6 And Target.Column <= 7) Then

If Target.Value = 0 Then

Worksheets("Employee").Range("e" & Target.Row). _
Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" & Target.Row). _
Interior.ColorIndex = xlNone

End
End If

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

ActiveSheet.Protect

Exit Sub
ErrorHandler:
Application.EnableEvents = True
' MsgBox Err.Number & " " & Err.Description
Resume Next
End Sub


" wrote:

The following code works fine. I believe I can use a do loop to make
the code more efficient. Can someone help? I appreciate it.

Private Sub Worksheet_Change(ByVal Target As Range)
getReportingCode

On Error GoTo ErrorHandler

ActiveSheet.Unprotect
Dim mnb As Integer

Dim asd, asd2, colb, cole, colf, colg, colh, coli2l, cole2l As
Integer
Application.EnableEvents = False


If Not Intersect(Target, Range("B7:L35")) Is Nothing Then

colb = Application.CountA(Worksheets("Employee").Range("b " &
Target.Row))
cole = Application.CountA(Worksheets("Employee").Range("e " &
Target.Row))
colf = Application.CountA(Worksheets("Employee").Range("f " &
Target.Row))
colg = Application.CountA(Worksheets("Employee").Range("g " &
Target.Row))
colh = Application.CountA(Worksheets("Employee").Range("h " &
Target.Row))
coli2l = Application.CountA(Worksheets("Employee").Range("i " &
Target.Row & ":" & "l" & Target.Row))
cole2l = Application.CountA(Worksheets("Employee").Range("i " &
Target.Row & ":" & "l" & Target.Row))


If (colb 0) Then
If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (cole 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (colf 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (colg 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (colh 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (coli2l 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If


End If



End If


Application.EnableEvents = True
Application.ScreenUpdating = True

ActiveSheet.Protect

Exit Sub
ErrorHandler:
Application.EnableEvents = True
' MsgBox Err.Number & " " & Err.Description
Resume Next
End Sub


Thanks.

Faye


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 23
Default It works but I need better code...
I tested it and couldn't make it work with your code. It does not turn
any field into yellow.

On Apr 9, 11:44 am, Joel wrote:
Is this better????

Private Sub Worksheet_Change(ByVal Target As Range)
getReportingCode

On Error GoTo ErrorHandler

ActiveSheet.Unprotect
Dim mnb As Integer

Application.EnableEvents = False

If Not Intersect(Target, Range("B7:L35")) Is Nothing Then

If Target.Column = 2 Or _
(Target.Column = 6 And Target.Column <= 7) Then

If Target.Value = 0 Then

Worksheets("Employee").Range("e" & Target.Row). _
Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" & Target.Row). _
Interior.ColorIndex = xlNone

End
End If

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

ActiveSheet.Protect

Exit Sub
ErrorHandler:
Application.EnableEvents = True
' MsgBox Err.Number & " " & Err.Description
Resume Next
End Sub



" wrote:
The following code works fine. I believe I can use a do loop to make
the code more efficient. Can someone help? I appreciate it.

Private Sub Worksheet_Change(ByVal Target As Range)
getReportingCode

On Error GoTo ErrorHandler

ActiveSheet.Unprotect
Dim mnb As Integer

Dim asd, asd2, colb, cole, colf, colg, colh, coli2l, cole2l As
Integer
Application.EnableEvents = False

If Not Intersect(Target, Range("B7:L35")) Is Nothing Then

colb = Application.CountA(Worksheets("Employee").Range("b " &
Target.Row))
cole = Application.CountA(Worksheets("Employee").Range("e " &
Target.Row))
colf = Application.CountA(Worksheets("Employee").Range("f " &
Target.Row))
colg = Application.CountA(Worksheets("Employee").Range("g " &
Target.Row))
colh = Application.CountA(Worksheets("Employee").Range("h " &
Target.Row))
coli2l = Application.CountA(Worksheets("Employee").Range("i " &
Target.Row & ":" & "l" & Target.Row))
cole2l = Application.CountA(Worksheets("Employee").Range("i " &
Target.Row & ":" & "l" & Target.Row))

If (colb 0) Then
If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (cole 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (colf 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (colg 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (colh 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (coli2l 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If

End If

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

ActiveSheet.Protect

Exit Sub
ErrorHandler:
Application.EnableEvents = True
' MsgBox Err.Number & " " & Err.Description
Resume Next
End Sub

Thanks.

Faye- Hide quoted text -

- Show quoted text -


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default It works but I need better code...
Can you put a break point in the code and tell me why it is not working?

Add break point by pressing F9 on first line
Private Sub Worksheet_Change(ByVal Target As Range)


Highlight one cell in workbook and the in the Fx box at top of worksheet
hight box and press enter. This will force the workbook to recognize the
change and go to break point.

moving tthe mouse over the variable will show there values or you can add
the variabble into the watch window by press right mouse on the items you
want to watch.


then step through code by press F8.


"fzl2007" wrote:

I tested it and couldn't make it work with your code. It does not turn
any field into yellow.

On Apr 9, 11:44 am, Joel wrote:
Is this better????

Private Sub Worksheet_Change(ByVal Target As Range)
getReportingCode

On Error GoTo ErrorHandler

ActiveSheet.Unprotect
Dim mnb As Integer

Application.EnableEvents = False

If Not Intersect(Target, Range("B7:L35")) Is Nothing Then

If Target.Column = 2 Or _
(Target.Column = 6 And Target.Column <= 7) Then

If Target.Value = 0 Then

Worksheets("Employee").Range("e" & Target.Row). _
Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" & Target.Row). _
Interior.ColorIndex = xlNone

End
End If

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

ActiveSheet.Protect

Exit Sub
ErrorHandler:
Application.EnableEvents = True
' MsgBox Err.Number & " " & Err.Description
Resume Next
End Sub



" wrote:
The following code works fine. I believe I can use a do loop to make
the code more efficient. Can someone help? I appreciate it.

Private Sub Worksheet_Change(ByVal Target As Range)
getReportingCode

On Error GoTo ErrorHandler

ActiveSheet.Unprotect
Dim mnb As Integer

Dim asd, asd2, colb, cole, colf, colg, colh, coli2l, cole2l As
Integer
Application.EnableEvents = False

If Not Intersect(Target, Range("B7:L35")) Is Nothing Then

colb = Application.CountA(Worksheets("Employee").Range("b " &
Target.Row))
cole = Application.CountA(Worksheets("Employee").Range("e " &
Target.Row))
colf = Application.CountA(Worksheets("Employee").Range("f " &
Target.Row))
colg = Application.CountA(Worksheets("Employee").Range("g " &
Target.Row))
colh = Application.CountA(Worksheets("Employee").Range("h " &
Target.Row))
coli2l = Application.CountA(Worksheets("Employee").Range("i " &
Target.Row & ":" & "l" & Target.Row))
cole2l = Application.CountA(Worksheets("Employee").Range("i " &
Target.Row & ":" & "l" & Target.Row))

If (colb 0) Then
If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (cole 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (colf 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (colg 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (colh 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (coli2l 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If

End If

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

ActiveSheet.Protect
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default It works but I need better code...
Noticed a small problem why it may not work try this

Private Sub Worksheet_Change(ByVal Target As Range)
getReportingCode

On Error GoTo ErrorHandler

ActiveSheet.Unprotect
Dim mnb As Integer

Application.EnableEvents = False


If Not Intersect(Target, Range("B7:L35")) Is Nothing Then


If Target.Column = 2 Or _
(Target.Column = 6 And Target.Column <= 7) Then

If Target.Value = 0 Then

Worksheets("Employee").Target. _
Interior.ColorIndex = 36
Else
Worksheets("Employee").Target. _
Interior.ColorIndex = xlNone

End
End If

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

ActiveSheet.Protect

Exit Sub
ErrorHandler:
Application.EnableEvents = True
' MsgBox Err.Number & " " & Err.Description
Resume Next
End Sub



"fzl2007" wrote:

I tested it and couldn't make it work with your code. It does not turn
any field into yellow.

On Apr 9, 11:44 am, Joel wrote:
Is this better????

Private Sub Worksheet_Change(ByVal Target As Range)
getReportingCode

On Error GoTo ErrorHandler

ActiveSheet.Unprotect
Dim mnb As Integer

Application.EnableEvents = False

If Not Intersect(Target, Range("B7:L35")) Is Nothing Then

If Target.Column = 2 Or _
(Target.Column = 6 And Target.Column <= 7) Then

If Target.Value = 0 Then

Worksheets("Employee").Range("e" & Target.Row). _
Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" & Target.Row). _
Interior.ColorIndex = xlNone

End
End If

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

ActiveSheet.Protect

Exit Sub
ErrorHandler:
Application.EnableEvents = True
' MsgBox Err.Number & " " & Err.Description
Resume Next
End Sub



" wrote:
The following code works fine. I believe I can use a do loop to make
the code more efficient. Can someone help? I appreciate it.

Private Sub Worksheet_Change(ByVal Target As Range)
getReportingCode

On Error GoTo ErrorHandler

ActiveSheet.Unprotect
Dim mnb As Integer

Dim asd, asd2, colb, cole, colf, colg, colh, coli2l, cole2l As
Integer
Application.EnableEvents = False

If Not Intersect(Target, Range("B7:L35")) Is Nothing Then

colb = Application.CountA(Worksheets("Employee").Range("b " &
Target.Row))
cole = Application.CountA(Worksheets("Employee").Range("e " &
Target.Row))
colf = Application.CountA(Worksheets("Employee").Range("f " &
Target.Row))
colg = Application.CountA(Worksheets("Employee").Range("g " &
Target.Row))
colh = Application.CountA(Worksheets("Employee").Range("h " &
Target.Row))
coli2l = Application.CountA(Worksheets("Employee").Range("i " &
Target.Row & ":" & "l" & Target.Row))
cole2l = Application.CountA(Worksheets("Employee").Range("i " &
Target.Row & ":" & "l" & Target.Row))

If (colb 0) Then
If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (cole 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (colf 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (colg 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (colh 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (coli2l 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If

End If

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

ActiveSheet.Protect
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 857
Default It works but I need better code...
Faye,

Give this a try.


Private Sub Worksheet_Change(ByVal Target As Range)
getReportingCode

On Error GoTo ErrorHandler

ActiveSheet.Unprotect
Dim mnb As Integer

Application.EnableEvents = False

Dim rng As Range
Dim c As Range

If Not Intersect(Target, Range("B7:L35")) Is Nothing Then
With Worksheets("Employee")
Set rng = .Range("B" & Target.Row & ",E" & Target.Row & ":H" &
Target.Row & ",I" & Target.Row & ":L" & Target.Row)
If Application.CountA(rng) 0 Then
For Each c In rng
If c.Column < 9 And c.Value = 0 Then
c.Interior.ColorIndex = 36
Else
c.Interior.ColorIndex = xlNone
End If
Next c
End If
End With
End If


Application.EnableEvents = True
Application.ScreenUpdating = True

ActiveSheet.Protect

Exit Sub
ErrorHandler:
Application.EnableEvents = True
' MsgBox Err.Number & " " & Err.Description
Resume Next
End Sub




--
Hope that helps.

Vergel Adriano


" wrote:

The following code works fine. I believe I can use a do loop to make
the code more efficient. Can someone help? I appreciate it.

Private Sub Worksheet_Change(ByVal Target As Range)
getReportingCode

On Error GoTo ErrorHandler

ActiveSheet.Unprotect
Dim mnb As Integer

Dim asd, asd2, colb, cole, colf, colg, colh, coli2l, cole2l As
Integer
Application.EnableEvents = False


If Not Intersect(Target, Range("B7:L35")) Is Nothing Then

colb = Application.CountA(Worksheets("Employee").Range("b " &
Target.Row))
cole = Application.CountA(Worksheets("Employee").Range("e " &
Target.Row))
colf = Application.CountA(Worksheets("Employee").Range("f " &
Target.Row))
colg = Application.CountA(Worksheets("Employee").Range("g " &
Target.Row))
colh = Application.CountA(Worksheets("Employee").Range("h " &
Target.Row))
coli2l = Application.CountA(Worksheets("Employee").Range("i " &
Target.Row & ":" & "l" & Target.Row))
cole2l = Application.CountA(Worksheets("Employee").Range("i " &
Target.Row & ":" & "l" & Target.Row))


If (colb 0) Then
If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (cole 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (colf 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (colg 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (colh 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (coli2l 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If


End If



End If


Application.EnableEvents = True
Application.ScreenUpdating = True

ActiveSheet.Protect

Exit Sub
ErrorHandler:
Application.EnableEvents = True
' MsgBox Err.Number & " " & Err.Description
Resume Next
End Sub


Thanks.

Faye


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 23
Default It works but I need better code...
It works great. I will need to look at your code to see where I need
to update so that it will unhighlight when you delete the number
(null). It wasn't included in my original code. I changed my code like
the following in order to accomplish it:

Private Sub Worksheet_Change(ByVal Target As Range)
getReportingCode

On Error GoTo ErrorHandler

ActiveSheet.Unprotect
Dim mnb As Integer

Dim asd, asd2, colb, cole, colf, colg, colh, coli2l, cole2l As
Integer
Application.EnableEvents = False


If Not Intersect(Target, Range("B7:L35")) Is Nothing Then

colb = Application.CountA(Worksheets("Employee").Range("b " &
Target.Row))
cole = Application.CountA(Worksheets("Employee").Range("e " &
Target.Row))
colf = Application.CountA(Worksheets("Employee").Range("f " &
Target.Row))
colg = Application.CountA(Worksheets("Employee").Range("g " &
Target.Row))
colh = Application.CountA(Worksheets("Employee").Range("h " &
Target.Row))
coli2l = Application.CountA(Worksheets("Employee").Range("i " &
Target.Row & ":" & "l" & Target.Row))
cole2l = Application.CountA(Worksheets("Employee").Range("i " &
Target.Row & ":" & "l" & Target.Row))


If (colb 0) Then
If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If

Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
Worksheets("Employee").Range("i" &
Target.Row).Interior.ColorIndex = xlNone
Worksheets("Employee").Range("j" &
Target.Row).Interior.ColorIndex = xlNone
Worksheets("Employee").Range("k" &
Target.Row).Interior.ColorIndex = xlNone
Worksheets("Employee").Range("l" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (colf 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (colg 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (colh 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (coli2l 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If

End If


End If


Application.EnableEvents = True
Application.ScreenUpdating = True

ActiveSheet.Protect

Exit Sub
ErrorHandler:
Application.EnableEvents = True
' MsgBox Err.Number & " " & Err.Description
Resume Next
End Sub


Thank you so very much for your time.







On Apr 9, 2:24 pm, Vergel Adriano
wrote:
Faye,

Give this a try.

Private Sub Worksheet_Change(ByVal Target As Range)
getReportingCode

On Error GoTo ErrorHandler

ActiveSheet.Unprotect
Dim mnb As Integer

Application.EnableEvents = False

Dim rng As Range
Dim c As Range

If Not Intersect(Target, Range("B7:L35")) Is Nothing Then
With Worksheets("Employee")
Set rng = .Range("B" & Target.Row & ",E" & Target.Row & ":H" &
Target.Row & ",I" & Target.Row & ":L" & Target.Row)
If Application.CountA(rng) 0 Then
For Each c In rng
If c.Column < 9 And c.Value = 0 Then
c.Interior.ColorIndex = 36
Else
c.Interior.ColorIndex = xlNone
End If
Next c
End If
End With
End If

Application.EnableEvents = True
Application.ScreenUpdating = True

ActiveSheet.Protect

Exit Sub
ErrorHandler:
Application.EnableEvents = True
' MsgBox Err.Number & " " & Err.Description
Resume Next
End Sub

--
Hope that helps.

Vergel Adriano



" wrote:
The following code works fine. I believe I can use a do loop to make
the code more efficient. Can someone help? I appreciate it.

Private Sub Worksheet_Change(ByVal Target As Range)
getReportingCode

On Error GoTo ErrorHandler

ActiveSheet.Unprotect
Dim mnb As Integer

Dim asd, asd2, colb, cole, colf, colg, colh, coli2l, cole2l As
Integer
Application.EnableEvents = False

If Not Intersect(Target, Range("B7:L35")) Is Nothing Then

colb = Application.CountA(Worksheets("Employee").Range("b " &
Target.Row))
cole = Application.CountA(Worksheets("Employee").Range("e " &
Target.Row))
colf = Application.CountA(Worksheets("Employee").Range("f " &
Target.Row))
colg = Application.CountA(Worksheets("Employee").Range("g " &
Target.Row))
colh = Application.CountA(Worksheets("Employee").Range("h " &
Target.Row))
coli2l = Application.CountA(Worksheets("Employee").Range("i " &
Target.Row & ":" & "l" & Target.Row))
cole2l = Application.CountA(Worksheets("Employee").Range("i " &
Target.Row & ":" & "l" & Target.Row))

If (colb 0) Then
If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (cole 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (colf 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (colg 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (colh 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If
End If

If (coli2l 0) Then
If (colb = 0) Then
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("b" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (cole = 0) Then
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("e" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colf = 0) Then
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("f" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colg = 0) Then
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("g" &
Target.Row).Interior.ColorIndex = xlNone
End If

If (colh = 0) Then
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = 36
Else
Worksheets("Employee").Range("h" &
Target.Row).Interior.ColorIndex = xlNone
End If

End If

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

ActiveSheet.Protect

Exit Sub
ErrorHandler:
Application.EnableEvents = True
' MsgBox Err.Number & " " & Err.Description
Resume Next
End Sub

Thanks.

Faye- Hide quoted text -

- Show quoted text -


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Code somewhat works. Please help? jsc3489 Excel Programming 4 December 2nd 05 07:51 PM
Why this code works and other do not? Newbie here. [email protected] Excel Programming 2 September 10th 05 01:12 PM
Code works for any WBK except the PMW Mark Tangard[_3_] Excel Programming 3 July 17th 05 11:54 AM
Why won't this code works ksnapp[_37_] Excel Programming 6 April 1st 04 01:44 PM
VBA Code Works in 97, Not in 2002 William[_2_] Excel Programming 11 August 12th 03 02:05 AM


All times are GMT +1. The time now is 07:31 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement