You are here:

Excel/Need help with VBA code

Advertisement


Question
QUESTION: I have created a timesheet in Excel 2013 but due to various versions of Excel users using it, I have it saved with an extension of .xls so everyone can use it with no problems.  I already have some VBA code in place for helping with simplifying the data entry of times and that was working fine until I added some code to perform some conditional formatting for me.  I copied and pasted the conditional format code right below the time conversion code and now I'm getting compile errors.  I understand very little about VBA and so I don't know how to fix it.

The code that I added that "broke it" is the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("$M$46:$P$46")) Is Nothing Then
Select Case Target.Value
   Case Q45 > 0: icolor = 6
   Case Else: icolor = xlNone
If Not Intersect(Target, Range("$M$50:$P$50")) Is Nothing Then
Select Case Target.Value
   Case Q49 > 0: icolor = 6
   Case Else: icolor = xlNone
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub

Cell M46 and M50 are merged cells so that is why their ranges run from M to P in the code above.  What I'm trying to accomplish is to shade cell M46 in yellow if cell Q45 is greater than 0 and to shade cell M50 in yellow if cell Q49 is greater than 0.  I would normally do something like this thru Conditional Formatting but because of the users that are in versions lower than 2007, I can only have 3 conditional formats and so I've already maxed out to this limitation.  I did the research to find out yellow on the color index is 6, but I'm not able to test out this new code because I'm getting an error:

Ambiguous name detected: Worksheet_Change

I've tried numerous things guessing at how to fix this but no luck.  I need both the time conversion code and the conditional formatting code but not sure how to have both since pasting one right under the other is now causing me errors.  Below is all of my code in its entirety:

Private Sub Worksheet_Change(ByVal Target As Range)
Sheet2.Unprotect Password:="Timesheets101"
Dim rngCell As Range, dblTime As Double
On Error GoTo ExitPoint
If Intersect(Target, Range("D10:R17,C30:D49")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each rngCell In Intersect(Target, Range("D10:R17,C30:D49")).Cells
   If rngCell.Value <> "" Then
       With rngCell
         dblTime = TimeValue(Format(Val(.Value), "00\:00"))
         dblTime = TimeSerial(Hour(dblTime) Mod 12, Minute(dblTime), 0)
         dblTime = dblTime + IIf(UCase(Right(rngCell.Value, 1)) = "P", 0.5, 0)
         .NumberFormat = "h:mm AM/PM"
         .Value = dblTime
       End With
   End If
Next rngCell
ExitPoint:
Application.EnableEvents = True
Sheet2.Protect Password:="Timesheets101", DrawingObjects:=True, Contents:=True, Scenarios:=True _
       , AllowFormattingCells:=True

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("$M$46:$P$46")) Is Nothing Then
Select Case Target.Value
   Case Q45 > 0: icolor = 6
   Case Else: icolor = xlNone
If Not Intersect(Target, Range("$M$50:$P$50")) Is Nothing Then
Select Case Target.Value
   Case Q49 > 0: icolor = 6
   Case Else: icolor = xlNone
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub

What am I doing wrong?  I swear one of these days I'm going to take a Visual Basic coding class.  Anyway, thanks in advance for any help you can offer me.

ANSWER: The error you've probably been getting is "Ambiguous name detected"... in Excel Ambiguous would mean there are TWO of something that there can only be ONE of.

So, you have put two Worksheet_Change events into a single sheet module, which can't be, there can only be ONE.  So if you have to separate ws_Change events that you want to work together, you have to merge all that code into one event.

This is my stab at that:
===================

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range, dblTime As Double, icolor As Integer

Sheet2.Unprotect Password:="Timesheets101"
Application.EnableEvents = False
On Error GoTo ExitPoint

If Not Intersect(Target, Range("D10:R17,C30:D49")) Is Nothing Then
   For Each rngCell In Intersect(Target, Range("D10:R17,C30:D49")).Cells
       If rngCell.Value <> "" Then
         With rngCell
         dblTime = TimeValue(Format(Val(.Value), "00\:00"))
         dblTime = TimeSerial(Hour(dblTime) Mod 12, Minute(dblTime), 0)
         dblTime = dblTime + IIf(UCase(Right(rngCell.Value, 1)) = "P", 0.5, 0)
         .NumberFormat = "h:mm AM/PM"
         .Value = dblTime
         End With
       End If
   Next rngCell
ElseIf Not Intersect(Target, Range("$M$46:$P$46")) Is Nothing Then
   Select Case Target.Value
       Case [Q45] > 0
         icolor = 6
       Case Else
         icolor = xlNone
   End Select
   Target.Interior.ColorIndex = icolor
ElseIf Not Intersect(Target, Range("$M$50:$P$50")) Is Nothing Then
   Select Case Target.Value
       Case [Q49] > 0
         icolor = 6
       Case Else
         icolor = xlNone
   End Select
   Target.Interior.ColorIndex = icolor
End If

ExitPoint:
Application.EnableEvents = True
Sheet2.Protect Password:="Timesheets101", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True

End Sub
====================


---------- FOLLOW-UP ----------

QUESTION: Thanks for such a quick response.  I've copied and pasted your answer and I'm no longer getting any Compile Errors BUT it's not coloring those 2 cells in yellow when the corresponding "other" cells are greater than zero.  I saw where you fixed my formatting with the brackets but is there something else wrong?  It seems like a such a simple thing but obviously something is not right about the code.  I've checked the cell designations and I did remove the $ signs from the code but can't get those 2 cells to highlight in yellow.  Is there a simpler or better way to accomplish this?  Thanks again!

ANSWER: Forgetting the code for a moment, describe verbally what is supposed to happen:

1) Every time something is changes on the sheet we check for the address of the changed cell
2) If the address is anywhere within D10:R17,C30:D49 then we format the entry as specific way
3) If the address is anywhere within M46:$P46 then we check cell Q45 and if it is > 0 then we color the changed cell yellow
3) If the address is anywhere within M50:$P50 then we check cell Q49 and if it is > 0 then we color the changed cell yellow

close?

---------- FOLLOW-UP ----------

Capture.jpg
Capture.jpg  
QUESTION: Anytime cell Q45 is greater than 0, I'd like to see cell M46 light up in yellow.  Q45 is actually a merged cell from Q45 thru R46.  M46 is a merged cell from M46 thru P46.

Anytime cell Q49 is greater than 0, I'd like to see cell M50 light up in yellow.  Q49 is also a merged cell from Q49 thru R50.  M50 is a merged cell from M50 thru P50.

Then when someone puts data in M46 or M50, the yellow shading is supposed to change to white via a conditional format.

I will attach an image to help make more sense.

Again, thank you for taking your time to help me.

Answer
How about this:


=============
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range, dblTime As Double, icolor As Integer

Sheet2.Unprotect Password:="Timesheets101"
Application.EnableEvents = False
On Error GoTo ExitPoint

If Not Intersect(Target, Range("D10:R17,C30:D49")) Is Nothing Then
   For Each rngCell In Intersect(Target, Range("D10:R17,C30:D49")).Cells
       If rngCell.Value <> "" Then
         With rngCell
         dblTime = TimeValue(Format(Val(.Value), "00\:00"))
         dblTime = TimeSerial(Hour(dblTime) Mod 12, Minute(dblTime), 0)
         dblTime = dblTime + IIf(UCase(Right(rngCell.Value, 1)) = "P", 0.5, 0)
         .NumberFormat = "h:mm AM/PM"
         .Value = dblTime
         End With
       End If
   Next rngCell
End If

ExitPoint:
If [Q45] > 0 Then
   Range("M46:P46").Interior.ColorIndex = 6
Else
   Range("M46:P46").Interior.ColorIndex = xlNone
End If
If [Q49] > 0 Then
   Range("M50:P50").Interior.ColorIndex = 6
Else
   Range("M50:P50").Interior.ColorIndex = xlNone
End If

Application.EnableEvents = True
Sheet2.Protect Password:="Timesheets101", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True

End Sub  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jerry Beaucaire

Expertise

Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files

Experience

Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files ===================== I have been offering free assistance as an Excel aid on many web sites for many years: (http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)

Education/Credentials
Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2016 About.com. All rights reserved.