Cell value dependency

[mpolitte]

Gosh,

My first two posts were answered so well, I'm trying for a third. I now have a worksheet with column G (named "Type") defined as a list (values are 1-5). If the user selects a "Type" value of 1 (16 bit integer), in cell G2, I want to set cell H2 to "1", cell I2 to "1", and to disable/lock/or grey out cells K2 thru V2.

If the user sets cell G2 to "4 - a float" or to "5 - a string" I want to disable cell I2 and M2 thru V2. I think you get the picture, depending on the value of cell G# I want to set values in columns H# thru V# or disable them.

Here's the tricky part. I'll have to be able to handle a user selected range such that if the user selects G2-G10 and sets those cells to "1", I'll want to act as detailed above (H2-H10 must be set to 1, I2-I10 must be set to 1 etc.) for columns H2-H10 thru V2-V10.

As always, thanks for your expedient and accurate replies!

Maureen

[Bob Phillips]

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit
Dim cell As Range

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each cell In Target

Select Case cell.Value

Case 1
cell.Offset(0, 1).Value = 1
cell.Offset(0, 2).Value = 1
cell.Offset(0, 4).Resize(1, 16).Interior.ColorIndex = 15

Case "4 - a float", "5 - a string"
cell.Offset(0, 3).Interior.ColorIndex = 15
cell.Offset(0, 6).Resize(1, 10).Interior.ColorIndex = 15

'etc
End Select
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[mpolitte]

Thanks once again. My data entry tool is working most excellently.