How to use Target in Worksheet_Change?

Karlbirger

Board Regular
Joined
Sep 21, 2011
Messages
68
Hi guys,

I've been trying to configure my formula in Worksheet_Change to only run when targeted cells are being changed (by using Target). I don't get it to work properly.

A cell value is supposed to change in column AK when the equivalent cell in column H is changed.
Please se my formula below.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = "H" Then
Dim LastRow As Long
Dim Cell As Range
LastRow = Range("H" & Rows.Count).End(xlUp).Row
For Each Cell In Range("H6:H" & LastRow)
    If Cell.Value = "Yes" Then
        Range("AK" & Cell.Row).Value = "1.00"
    Else
    End If
Next Cell
End If

End Sub


Anyone used to work with Worksheet_Change Target who can see how I use Target wrong?

Regards
KB
 

Some videos you may like

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)

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Hello

The Column property will return numbers, so number 8 for column H.

You need to test for Target.Column = 8, as a consequence.

Furthermore, you do not need the loop through column H cells, since... you know what cell was changed (Target).

One thing to add is that Target.Count should be 1: only 1 cell should be changed. If not, Target.Column will give you an error (for instance, change cells H1:I5 all at once and Target.Column will fail).
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 8 And Target.Value = "Yes" Then
    Application.EnableEvents = False
    Target.Offset(, 29).Value = 1
    Application.EnableEvents = True
End If
End Sub
 

Karlbirger

Board Regular
Joined
Sep 21, 2011
Messages
68
wigi, VoG

Thanks for the lecture.

Is it possible to select an entire row and e.g paint it with a colour within this formula, instead of just selecting the column AK (Target.Offset(, 29))?

Code:
If Target.Column = 8 And Target.Value = "Yes" Then
    Application.EnableEvents = False
    Target.Offset(, 29).Value = 1
    Application.EnableEvents = True
End If

Regards
KB
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

Try

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 8 And Target.Value = "Yes" Then
    Target.EntireRow.Interior.ColorIndex = 6
End If
End Sub
 

Karlbirger

Board Regular
Joined
Sep 21, 2011
Messages
68
Great, thx VoG and wigi.

My formula now looks as the following:


Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 8 And Target.Value = "Yes" Then
    Application.EnableEvents = False
    Target.Offset(, 29).Value = 1
    Application.EnableEvents = True
End If

If Target.Column = 8 And Target.Value = "No" Then
    Application.EnableEvents = False
    Target.Offset(, 29).Value = 0.25
    Application.EnableEvents = True
End If

If Target.Column = 1 And Target.Value = "Dead" Then
    Target.Offset(, 36).Value = 0
    Target.EntireRow.Interior.ColorIndex = 48
End If

If Target.Column = 1 And Target.Value = "Completed" Then
    Target.EntireRow.Interior.ColorIndex = 37
End If

If Target.Column = 1 And Target.Value = "Active" Then
    Target.Offset(, 36).Value = 0.25
    Target.EntireRow.Interior.ColorIndex = 2
End If

End Sub


Is it a waste to build the formulas into 5 differeny ones, or is it possible to combine them into perhaps 2 formulas with extended if functions?
Or is this a fully acceptable edited formula given its purpose?

Regards
KB
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

For instance:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    With Target

        If .Count = 1 Then

            If .Column = 8 Then
                .Offset(, 29).Value = IIf(.Value = "Yes", 1, 0.25)
            ElseIf .Column = 1 Then
                If .Value = "Dead" Then
                    .Offset(, 36).Value = 0
                    .EntireRow.Interior.ColorIndex = 48
                ElseIf .Value = "Completed" Then
                    .EntireRow.Interior.ColorIndex = 37
                ElseIf .Value = "Active" Then
                    .Offset(, 36).Value = 0.25
                    .EntireRow.Interior.ColorIndex = 2
                End If
            End If

        End If

    End With

End Sub

Why do you colour an entire row?

Select Case is an alternative do multiple IF structures.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 8 Then
    Application.EnableEvents = False
    Select Case Target.Value
        Case "Yes": Target.Offset(, 29).Value = 1
        Case "No": Target.Offset(, 29).Value = 0.25
    End Select
    Application.EnableEvents = True
End If

If Target.Column = 1 Then
    Application.EnableEvents = False
    Select Case Target.Value
        Case "Dead": Target.Offset(, 36).Value = 0: Target.EntireRow.Interior.ColorIndex = 48
        Case "Completed": Target.EntireRow.Interior.ColorIndex = 37
        Case "Active": Target.Offset(, 36).Value = 0.25:   Target.EntireRow.Interior.ColorIndex = 2
    End Select
    Application.EnableEvents = True
End If

End Sub
 

Karlbirger

Board Regular
Joined
Sep 21, 2011
Messages
68
Thanks for a quick answer wigi.

The reason I want to color an entire row is to make it easy to get a visual overview over different ongoing projects, whether they are "Active", "Completed" or "Dead".

I work with quite many projects at the same time, why I find this solution great for having a quick overview over the general status.

Regards
KB
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Thanks for a quick answer wigi.

The reason I want to color an entire row is to make it easy to get a visual overview over different ongoing projects, whether they are "Active", "Completed" or "Dead".

I work with quite many projects at the same time, why I find this solution great for having a quick overview over the general status.

Regards
KB

I agree, but probably 20/50/100 columns could suffice. Excel 2007/2010 have 16384 columns...
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

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
Top