Conditional formatting

[Aliena]

Hi all, this is my question.

In a worksheet I need to establish a condition that only allows me to edit certain cells only if another cell is equal to a value. For example:

-- If A1="Hour" the worksheet allows me to edit C1 and D1, but doesn't allow me to edit E1.

-- But if A1="Day", the worksheet doesn't allow me to edit C1 and D1 but allows me to edit E1.

note: A1 can only contain "Hour" or "Day" no other value is allowed.

I don't know if I made myself clear...if not, please tell me and I'll try to explain it clearly. Sorry for my english... and thanks in advance.

 

[Andrew Fergus]

Hi
Right click the tab of the worksheet you are working on and click the option 'View Code' - this will open the Visual Basic Editor (VBE) screen. Copy and paste the following code into the VBE screen :

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Error_Handler

Select Case Target.Address
    Case "$A$1"
        If Target.Value <> "Hour" And Target.Value <> "Day" Then
            UndoMyEdit
            MsgBox "Cell A1 must be either 'Hour' or 'Day'.", vbInformation, "Input Error"
        End If
    Case "$C$1", "$D$1"
        If Cells(1, "A").Value = "Day" Then
            UndoMyEdit
            MsgBox "You cannot change this cell!", vbInformation, "Input Error"
        End If
    Case "$E$1"
        If Cells(1, "A").Value = "Hour" Then
            UndoMyEdit
            MsgBox "You cannot change this cell!", vbInformation, "Input Error"
        End If
End Select
    
Exit_Here:
    Exit Sub

Error_Handler:
    MsgBox Err.Description, vbCritical, "Error # " & Err.Number
    Resume Exit_Here

End Sub


Private Function UndoMyEdit()
With Application
    .EnableEvents = False
    .Undo
    .EnableEvents = True
End With
End Function

Save and close the VBE screen. Test this works as expected.

HTH, Andrew

 

[Peter_SSs]

Aliena

Your Post Subject is misleading as it is Data Validation that you need rather than Conditional Formatting. Try this:

1. Select C1:D1
2. Data|Validation...|Allow: Custom|Formula: =$A$1="Hour"|OK
3. Select E1
4. Data|Validation...|Allow: Custom|Formula: =$A$1="Day"|OK