How do I disable cells based on a previous input

[kaskade]

I am trying to create an advanced spreadsheet in excel that caters for people who really need to be told exactly what to do and what not to do. So based on the selection in column A I want to disable and change the background colour of some cells as follows (but only disable the cells on the same row):

<code>columnA = Number then disable cells D, F, G and change to red
</code>
<code>columnA = </code><code>Link then disable cells E, F, G and change to red
</code>
<code>columnA = </code><code>Image then disable cells D, E and change to red

</code> Any help much appreciated, I really my excel/vba skills are really not up to much.

 

[DonkeyOte]

Far from elegant but perhaps something along the lines of:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngInterest As Range, rngCell As Range
    Dim strCols As String
    Const C_WS_PWD = "password"
    Set rngInterest = Intersect(Target, Columns("A"))
    If Not rngInterest Is Nothing Then
        For Each rngCell In rngInterest.Cells
            Select Case UCase(rngCell.Value)
                Case "NUMBER"
                    strCols = "D:D,F:G"
                Case "LINK"
                    strCols = "E:G"
                Case "IMAGE"
                    strCols = "D:E"
                Case Else
                    strCols = ""
            End Select
            If strCols <> "" Then
                Me.Unprotect C_WS_PWD
                With Intersect(rngCell.EntireRow, Range("D:G"))
                    .Locked = False
                    .Cells.Interior.ColorIndex = xlNone
                End With
                With Intersect(rngCell.EntireRow, Range(strCols))
                    .Locked = True
                    .Cells.Interior.ColorIndex = 3
                End With
                Me.Protect C_WS_PWD
            End If
        Next rngCell
    End If
End Sub

To install the above right click on the sheet to which you wish to apply the code, choose View Code and paste above into resulting window.

Modify C_WS_PWD to be whichever password you want to apply to the worksheet.
(protect the VB Project Model as appropriate)

NOTE:

The above code assumes that columns of interest are A, D:G and that prior to the worksheet being protected for the first time these columns have been set as unlocked (VBA will lock as appropriate)

The code should also account for the possibility of multiple cells being altered simultaneously and possibly via a single formula (thus potentially generating different results in each cell of affected range)