Solved

Adding to a VBA?

Posted on 2016-10-12
6
93 Views
Last Modified: 2016-10-12
I have the following VBA that I want to add the exact same execution to, for cells B23 through O34. I am a complete newbie rookie to doing anything with these types of things ... but I do know the below works, and I just want to know how to add these cells to the same operation. Can you tell me how to it edit? Thanks so much in advance!:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A7:O34")) Is Nothing Then
        On Error GoTo FallThrough
        Application.EnableEvents = False
        Dim a As Range
        For Each a In Intersect(Target, Range("A7:O34"))
            If IsNumeric(a.Value) Then _
                If a.Value > 1 And a.Value Mod 100 < 60 And Int(a.Value / 100) < 24 Then _
                    a = TimeValue(Int(a.Value / 100) & ":" & Format(a.Value Mod 100, "00"))
        Next a
    End If
FallThrough:
    Application.EnableEvents = True
End Sub
0
Comment
Question by:Cactus1994
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41841312
Notice the Range mentioned in the following line of code...
If Not Intersect(Target, Range("A7:O34")) Is Nothing Then
The code will be effective for this range so if you want this code to work for the range B23:O34, change the range portion like this...
If Not Intersect(Target, Range("B23:O34")) Is Nothing Then
0
 

Author Comment

by:Cactus1994
ID: 41841316
Hi Subodh. Thanks -- I should have added more detail.

I only want the cell ranges B7:O18 and also B23:O34 in this VBA.  (I do not want cells B19:O22 included.)

Thanks!
0
 

Author Comment

by:Cactus1994
ID: 41841322
My question should have looked like this:


I have the following VBA I want to exclude cells A19:O22 from. How do I do this?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A7:O34")) Is Nothing Then
        On Error GoTo FallThrough
        Application.EnableEvents = False
        Dim a As Range
        For Each a In Intersect(Target, Range("A7:O34"))
            If IsNumeric(a.Value) Then _
                If a.Value > 1 And a.Value Mod 100 < 60 And Int(a.Value / 100) < 24 Then _
                    a = TimeValue(Int(a.Value / 100) & ":" & Format(a.Value Mod 100, "00"))
        Next a
    End If
FallThrough:
    Application.EnableEvents = True
End Sub
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 31

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41841329
Change that line to this...
If Not Intersect(Target, Range("B7:O18,B23:O34")) Is Nothing Then
0
 

Author Closing Comment

by:Cactus1994
ID: 41841333
Wow ... it was that easy. I told you I was a complete rookie newbie to VBAs!

Thank you!
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41841334
Yes it was that easy. :)
You're welcome. Glad to help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question