Worksheet Change code, need a little help

[larrycav]

This deals with formatting cells based on Metric or Imperial Values

On shee1t I have this code and it also works perfectly. The set Units = Range("J6") is driven by a simple validation list selection with two choices, Metric or Inch.

Private Sub Worksheet_Change(ByVal Target As Range)
' This sets up the cell formatting for number of decimal place values when using inch vs mm for checking lift values and increments

Dim Units As Range, Lifts As Range, r As Range
Set Units = Range("j6")
Set Lifts = Range("k7,k8,b7:b31,h13:h28")
If Intersect(Target, Units) Is Nothing Then Exit Sub
Target.Offset(6, 10).Select
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If Target.Value = "Metric" Then
    For Each r In Lifts
       ' r.Value = 25.4 * r.Value
        r.NumberFormat = "0"
    Next r
Else
    For Each r In Lifts
       ' r.Value = r.Value * 0.0393701
        r.NumberFormat = "0.000"
    Next
End If
Application.EnableEvents = True

On Sheet3 I have the following code and it works perfectly. Again, the Set Units = Range("H1") is driven by a simple list with the same choices, Metric or Inch. To create the list I used Data Validation that uses a Named Range. That Named Range is actually comprised of the same two cells on Sheet1 that sheet1 uses.

Objective:
What I would like to do is to eliminate having to make selections from both lists. I would like the user to be able to choose the selection on Sheet1 and have it carry over to Sheet3. I can't make it work.

Among other things, I tried eliminating the list on Sheet3 and making that cell value =Sheet1!J6...No go. It's odd to me that the code works with a validation list that is driven by a named range on Sheet1, yet won't work when I make ("H1") = Sheet1!HJ6. How can I reach my objective?

Private Sub Worksheet_Change(ByVal Target As Range)
' This sets up the cell formatting for number of decimal place values when using inch vs mm for checking lift values and increments
Dim Units As Range, Lifts2 As Range, r2 As Range
Set Units = Range("H1")
Set Lifts2 = Range("a7:a31,a35:a59,a63:a87,a91:a116,a120:a144,a148:a172,a176:a200,a204:a228,a232:a256,a260:a284,a288:a312,a316:a340")
If Intersect(Target, Units) Is Nothing Then Exit Sub
Target.Offset(1, 8).Select
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If Target.Value = "Metric" Then
    For Each r2 In Lifts2
       r2.NumberFormat = "0"
 Next r2
Else
    For Each r2 In Lifts2
    r2.NumberFormat = "0.000"
    Next
    End If
Application.EnableEvents = True
End Sub

 

[Teeroy]

I think the problem may be that your Target for the Change Event on Sheet3 is not H1 anymore; you don't go to that cell anymore so it is not in the intersect and the sub exits before the rest of the code is run.
Since the value change is now on another sheet you could run the Units check on the Worksheet_Activate Event i.e.

Private Sub Worksheet_Activate()
' This sets up the cell formatting for number of decimal place values when using inch vs mm for checking lift values and increments
Dim Units As String, Lifts2 As Range, r2 As Range
Units = Sheets("Sheet1").Range("H1").Value
Set Lifts2 = Range("a7:a31,a35:a59,a63:a87,a91:a116,a120:a144,a148:a172,a176:a200,a204:a228,a232:a256,a260:a284,a288:a312,a316:a340")
If Units = "" Then Exit Sub
Application.EnableEvents = False
If Units = "Metric" Then
    For Each r2 In Lifts2
       r2.NumberFormat = "0"
    Next r2
Else
    For Each r2 In Lifts2
       r2.NumberFormat = "0.000"
    Next
End If
Application.EnableEvents = True
End Sub

 

[larrycav]

Call this one SOLVED. I had to adjust your code a tad bit but it works nicely. THANK YOU!