Page 1 of 2 12 LastLast
Results 1 to 10 of 12

help with vba

This is a discussion on help with vba within the Excel Questions forums, part of the Question Forums category; Hi The problem here is I'm trying to run 2 separate pieces of code independently within the same sub. The ...

  1. #1
    Board Regular
    Join Date
    Jan 2015
    Posts
    167

    Default help with vba

    Hi

    The problem here is I'm trying to run 2 separate pieces of code independently within the same sub. The first part of the code changes the row colour when range AA = "closed"

    The second part enters a message in column D when C = "Y" or "N"


    Any help is appreciated.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    
    On Error Resume Next
    
    
    Dim i As Long, r1 As Range, r2 As Range
    
    
       For i = 2 To 1000
          Set r1 = Range("aa" & i)
          Set r2 = Range("A" & i & ":ab" & i)
          If r1 = "closed" Then r2.Interior.ColorIndex = 15
    
    
    
    
          
          Exit Sub
          
          
       Next i
    
    
    
    
        
        If Intersect(Target, Range("c2:c100000")) Is Nothing Then
            Exit Sub
        End If
    
    
        Application.EnableEvents = False
    
    
        If Target.Value = "Y" Then
            Target.Offset(, 1).Value = "Key in the dependency ID"
        Else
            Target.Offset(, 1).Value = "N/A"
        End If
    
    
        Application.EnableEvents = True
    
    
    End Sub

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    71,984

    Default Re: help with vba

    What is the problem with the code?

    One thing I can see that might be an issue is that the first loop will only run once and then the entire sub wil be exited.
    If posting code please use code tags.

  3. #3
    Board Regular wideboydixon's Avatar
    Join Date
    Jun 2016
    Location
    Sheffield, UK
    Posts
    1,971

    Default Re: help with vba

    Remove the line that says "Exit Sub".

    WBD
    Excel 2016 on Windows 10.
    Please use [code] tags when posting code.
    Please post data in proper tables - see here for some tools.
    Marvin: I've been communicating with the ship.
    Ford: What did it say?
    Marvin: It hates me.


  4. #4
    Board Regular
    Join Date
    Jan 2015
    Posts
    167

    Default Re: help with vba

    Thanks for the help

    That has partly worked. What I'm trying to achieve is basically 2 separate and independent if statements

    the first condition is if "Y" is in entered in Range C then text will appear if D (Using the offset function)
    the second is if any cell in range AA equals closed then the whole should appear grey (colourindex 15)

    the first condition works perfect, its second one that's not working entirely.

    Much appreciated

  5. #5
    Board Regular wideboydixon's Avatar
    Join Date
    Jun 2016
    Location
    Sheffield, UK
    Posts
    1,971

    Default Re: help with vba

    Try this instead:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim testRange As Range
    Dim testCell As Range
    
    Set testRange = Application.Intersect(Target, Range("AA:AA"))
    
    If Not testRange Is Nothing Then
        For Each testCell In testRange
            If testCell.Value = "closed" Then
                Range("A" & testCell.Row & ":AB" & testCell.Row).Interior.ColorIndex = 15
            End If
        Next testCell
    End If
    
    Set testRange = Application.Intersect(Target, Range("C:C"))
    
    If Not testRange Is Nothing Then
        Application.EnableEvents = False
        For Each testCell In testRange
            If testCell.Value = "Y" Then
                testCell.Offset(, 1).Value = "Key in the dependency ID"
            Else
                testCell.Offset(, 1).Value = "N/A"
            End If
        Next testCell
        Application.EnableEvents = True
    End If
    
    End Sub
    WBD
    Excel 2016 on Windows 10.
    Please use [code] tags when posting code.
    Please post data in proper tables - see here for some tools.
    Marvin: I've been communicating with the ship.
    Ford: What did it say?
    Marvin: It hates me.


  6. #6
    Board Regular
    Join Date
    Jan 2015
    Posts
    167

    Default Re: help with vba

    that works perfect many thanks... You may get asked this question frequently but what would you say the best resources are to learn VBA?

  7. #7
    Board Regular wideboydixon's Avatar
    Join Date
    Jun 2016
    Location
    Sheffield, UK
    Posts
    1,971

    Default Re: help with vba

    Hi,

    I can't really answer that question because I'm self-taught. Sites like this and others (OzGrid, Experts Exchange, Stack Overflow) are a great help when you browse through them looking at solutions to problems. I usually manage to learn something new every day even though I have lots of years of experience.

    WBD
    Excel 2016 on Windows 10.
    Please use [code] tags when posting code.
    Please post data in proper tables - see here for some tools.
    Marvin: I've been communicating with the ship.
    Ford: What did it say?
    Marvin: It hates me.


  8. #8
    Board Regular
    Join Date
    Jan 2015
    Posts
    167

    Default Re: help with vba

    Hello

    Many thanks for this code. Worked a treat.

    One question, rather than this line
    Code:
    testCell.Offset(, 1).Value = "Key in the dependency ID"
    
    
    I would like it to read as below:

    Code:
     sheets("Dependancies").Range("A1").select
    I've tried replacing it but it doesn't work. Many thanks
    Last edited by p4nny; Jun 19th, 2017 at 07:00 AM.

  9. #9
    Board Regular wideboydixon's Avatar
    Join Date
    Jun 2016
    Location
    Sheffield, UK
    Posts
    1,971

    Default Re: help with vba

    Hi,

    I'm not sure what you're trying to achieve with that line. Have you checked the spelling of the sheet name? You probably want to activate the sheet before trying to select a cell on it.

    Code:
    Sheets("Dependancies").Activate
    Sheets("Dependancies").Range("A1").Select
    WBD
    Excel 2016 on Windows 10.
    Please use [code] tags when posting code.
    Please post data in proper tables - see here for some tools.
    Marvin: I've been communicating with the ship.
    Ford: What did it say?
    Marvin: It hates me.


  10. #10
    Board Regular
    Join Date
    Jan 2015
    Posts
    167

    Default Re: help with vba

    thanks, I didn't do the 'Activate 'function

    Just trying little enhancements now to sheet.

    I'm trying to wrap the "find" function around the code below but cannot seem to get it working. (Lookin:xlvalues)

    I would like to select the cell once it has found the match with the Dependencies sheet

    Again, thanks for your support

    Code:
    testcell.offset(,-1)

Page 1 of 2 12 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com