help with vba

p4nny

Board Regular
Joined
Jan 13, 2015
Messages
246
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
that works perfect many thanks... You may get asked this question frequently but what would you say the best resources are to learn VBA?
 
Upvote 0
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
 
Upvote 0
Hello

Many thanks for this code. Worked a treat.

One question, rather than this line
Code:
[COLOR=#333333]testCell.Offset(, 1).Value = "Key in the dependency ID"

[/COLOR]
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:
Upvote 0
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.

Rich (BB code):
Sheets("Dependancies").Activate
Sheets("Dependancies").Range("A1").Select

WBD
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top