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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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).
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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