How to lock one cell out of three?

[rangel]

Hi,
First let me thank you for taking the time to read this thread.
I am very new in excel, I have 3 cells (A1, A2, A3) and I would like to know if it is possible to lock one out of the three when two have data. for example;
If I have A1="any number" and A3="any number" I want A2 to be locked/grey out. If I have A2="any number" and A3="any number" I want A1 to be locked.
any help will be greatly appreciated, Thanks in advance!!

 

[Eric W]

Re: Help Please! How to lock one cell out of three?

Welcome to the Board!

Try this: select A1:A3. Go to the Data tab and click Data Validation. Select Custom, and in the formula box, put this:

=COUNTA($A$1:$A$3)<3

Click the "Error Alert" tab and put in a custom error message if you want. Let us know if this works for you.

If you want to "gray out" the extra cell, you can use Conditional Formatting. Select A1:A3 again. From the Home tab, click Conditional Formatting > New Rule > Use a formula > and enter:

=AND(A1="",COUNTA($A$1:$A$3)=2)

Select a fill color and click OK.

 

[Rick Rothstein]

Re: Help Please! How to lock one cell out of three?

Here is an event procedure which should be acceptable to you... it grays the third cell when two others have an entry in them, but it does not lock the third cell per se, rather what it does is have that cell reject any entry the user tries to make in it. Give it a try and see if you like how it works...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) Like "A[1-3]" Then
    Select Case [COUNTA(A1:A3)]
      Case 0, 1
        Range("A1:A3").Interior.ColorIndex = xlNone
      Case 2
        Range("A1:A3").SpecialCells(xlBlanks).Interior.ColorIndex = 15
      Case 3
        Application.Undo
    End Select
  ElseIf Intersect(Target, Range("A1:A3")).Address = Range("A1:A3").Address Then
    Range("A1:A3").Interior.ColorIndex = xlNone
  End If
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[rangel]

Re: Help Please! How to lock one cell out of three?

thank you Eric! it is what I needed on locking the cell, the gray out part of it works but it greys out the three cells.

Welcome to the Board!

Try this: select A1:A3. Go to the Data tab and click Data Validation. Select Custom, and in the formula box, put this:

=COUNTA($A$1:$A$3)<3

Click the "Error Alert" tab and put in a custom error message if you want. Let us know if this works for you.

If you want to "gray out" the extra cell, you can use Conditional Formatting. Select A1:A3 again. From the Home tab, click Conditional Formatting > New Rule > Use a formula > and enter:

=AND(A1="",COUNTA($A$1:$A$3)=2)

Select a fill color and click OK.

 

[Rick Rothstein]

Re: Help Please! How to lock one cell out of three?

thank you Eric! it is what I needed on locking the cell, the gray out part of it works but it greys out the three cells.

My previous message overlapped you last message, so you may have missed it. Does what I posted in Message #3 work for you?

 

[rangel]

Re: Help Please! How to lock one cell out of three?

Thank you Rick, it is working for me! can I change the color?
thanks for all the help!

My previous message overlapped you last message, so you may have missed it. Does what I posted in Message #3 work for you?

 

[Rick Rothstein]

Re: Help Please! How to lock one cell out of three?

Thank you Rick, it is working for me! can I change the color?

Your original message said you wanted the cell to be gray, so I made it gray. Of course, I can change it if you want... what color do you actually want to use?

 

[Eric W]

Re: Help Please! How to lock one cell out of three?

I'm a little puzzled, the CF highlighting works fine for me. Did you select A1:A3 together, and enter the formula 1 time, or did you select each individual cell and enter the formula 3 times? If you selected A1:A3, did you start with A1 and drag down, or did you start with A3 and drag up? If you selected A1:A3 (starting with A1), and entered the formula once, did you enter the formula exactly as written? You didn't change any of the $ characters?

There's probably a simple change to get it to work, the trouble is just finding it. Let me know if any of the above questions help identifying the issue, and if you want to figure it out, or if you're happy with Rick's event handler.

 

[rangel]

Re: Help Please! How to lock one cell out of three?

I would prefer red if is an easy change sir, thank you.

Your original message said you wanted the cell to be gray, so I made it gray. Of course, I can change it if you want... what color do you actually want to use?

 

[Rick Rothstein]

Re: Help Please! How to lock one cell out of three?

I would prefer red if is an easy change sir, thank you.

Change the 15 to 3. If you want to see all of the Color Index values you can choose from, copy/paste this code into the VB editor's Immediate Window (press CTRL+G if you don't see it)...

For X = 1 To 56: Cells(X, "A").Interior.ColorIndex = X: Next

and hit the Enter key... all of the possible ColorIndex colors will be shown in Column A of the active sheet... find a color you like and replace the 15 in my code with row number for that color.