asked on

Unlocking cells and modifying cell values

Folks,
This is a snippet of code that is not working:

Private Sub cmdCkAnserAverageCon_Click()
   If Range("D7").Text <> "13.23" Then
   Cells.Select.Range("E7").Locked = False
   Cells.Select.Range("E7").Font = White
   Cells.Select.Range("E7").Background = Red
   Cells.Select.Range("E7").Value = "Error"
   CheckFormulaFunction
    Exit Sub
    End If
        
    FormulasOK
    Cells.Select.Range("E7:E15").Locked = True
    Cells.Select.Range("E7:E15") = Clear

End Sub

Open in new window

Here's what I'd like to do. If the value in D7 does not agree with what it should be the formula is incorrect. The worksheet is protected except where the user enters their formulas (this is for training purposes). If it does not agree then in the above example I like to unprotect E7, change its background to red, the font to white and its value to be "Error".
If all the formulas are OK then make sure the contents of cells from E7:E15 are cleared and that all cells from E7:E15 are locked

SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

byundt

I see that ssaqibh caught some of the same bugs I did, but posted them first. My apologies.

Frank Freese

ASKER

Everything above this looks good.
Here's I would like to restore to cells E7:E15 to their defaults. This did not work
Range("E7:E15").Locked = True
Range("E7:E15") = Clear

I changed to this and it looks to be working.
Range("E7:E15").Font.Color = vbBlack
Range("E7:E15").Interior.Color = vbWhite
Range("E7:E15").Value = " "
Range("E7:E15").Locked = True

Frank Freese

ASKER

Thanks folks - I appreciate your comments and help very much