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

Asked By Rajender Prasad on 20-Nov-12 09:49 AM
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

Harry Boughen replied to Rajender Prasad on 20-Nov-12 02:41 PM
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?


Rajender Prasad replied to Harry Boughen on 21-Nov-12 02:28 AM
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.

Harry Boughen replied to Rajender Prasad on 21-Nov-12 05:19 AM
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.


Harry Boughen replied to Rajender Prasad on 21-Nov-12 06:15 AM
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

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

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.