Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > It works but I need better code...
 
 
LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #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
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Threaded Mode Threaded 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 11:53 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement