Microsoft Excel - Pls Help!!! Diable a Cell based on a condition

Dear Friends,

  I have a column I, if any of the Cell in I colmn is blank, respective Cell of Coulmn L should be disable.

Please help

Regards,
Prasad

Hello Prasad,

One option would be to use conditional formatting to colour the cell in ColumnL based on the contents of ColumnI to warn users not to enter anything in the cell.

To change the actual protection status of the cell would require a macro (event triggered perhaps).  So I guess the question is, is the change in status due to the user actually deleting something from ColumnI or not actually trying to enter something in ColumnI?

Regards

Harry

The process will be like below:

We will download the excel from an application, once we open the excel there were some existe macros those will be enabled.
And there is a chance that Coulmn I Can be blank while downloading, for such cells respective Coulmn L cell should disable, user should not input any value into the same.

Hope am clean a little now.

Prasad

Hello Prasad,

Sounds like you will need a WorkBook_Open macro that will find the last entry in ColumnI, then step through each cell in the range and change the protection status of the corresponding cell in ColumnL if the cell is empty.

Regards

Harry

Hello Prasad,

This will give you some idea of what is needed.  It has to be placed in the Workbook code area.

Private Sub EntryControl()
Dim LastRow As Long
Dim rngColI, c, rngUsed As Range

ActiveSheet.Unprotect
Set rngUsed = Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell))
rngUsed.Locked = False

With ActiveSheet
    LastRow = .Cells(.Rows.Count, "I").End(xlUp).Row
End With
Set rngColI = Range("I1:I" & LastRow)
For Each c In rngColI
    If c.Value = "" Then
      c.Offset(0, 3).Locked = True
    End If
Next c
ActiveSheet.Protect

End Sub

Also for the block to be effective the relevant sheet has to be protected.  This also only works for the sheet that opens first when the file is opened.  If you want it to work on a specific sheet or multiple sheets you will have to modify it to suit.  It also only protects down to the last entry in columnI and not to the possible bottom of data in your spreadsheet.  That will need a little more work that I cannot do at the moment but you could fix that yourself.

Regards

Harry