Conditional Formatting - How can I mimic this??? 2

[AustinMan]

I used Conditional Formating on a cell. Set the formula to change format based on value of cell.

Here is a procedure with the attentions I want.

Sub FormatUnprotected()
For Each Item In ActiveCell
If Item.Locked = False And IsEmpty(Item.Value) = True Then
Item.Interior.ColorIndex = 20
Else
Item.Interior.ColorIndex = 0
End If
Next
End Sub

I placed the call on Worksheet Change and Worksheet Selection Change; however, when the sheet is protected. If I delete the contents, using delete button, the change event is activated.

When I enter the value and tab through the next unlocked cell, it does not change format until I go into the cell again (Selection Change) or hit enter. How does the Conditional Format work when I enter those conditions? It does the change automatically. I may have around 100 cells to CF. Is it possible to mimic that??

Any guidance who be great!!

Thanks!!

 

[SteveBausch]

Are you familiar with

 Application.EnableEvents=False

?

I have had the same sort of "Gotcha" when using _Change, and the key is toggling Application.EnableEvents.

 

[sdraper]

That's because the active cell is the cell you are moving to not the cell that you just came form.
sdraper

 

[AustinMan]

Ok, So what object or event can I use to represent changes to the cell if activecell is not adequate in a protected sheet. I want the user not to use enter, but tab through.

When the sheet is protected, using the conditional formatting for the cell values works when you step off the active cell to another unlocked cell (this scenario).

What can I use, a combination of events and object(s) that mimic that process while sheet in protected.

Thanks again for your quick responses.

I have used application.enableevents with no avail

 

[Zathras]

Why not just use conditional formatting?

 

[AustinMan]

I may have around over 100, maybe 200 cells to Conditional Format.

My fear is that the workbook size would bloat big time.

Is this true?

 

[sdraper]

Zathras
I know conditional formattiong can test to see if the ceel is empty but can it test if the cell is locked?
sdraper

 

[AustinMan]

I want this sheet to be protected.

Once protected, I could tab through only the unlocked cells.

I can manually conditional format the cells but It seems lot there should be an easier way.

Thanks!

 

[Zathras]

Good idea to be aware of size issues. In this case however, it is only a matter of your time to set it up. I just did a quick compare between a workbook with 1 CF cell and a workbook with 200 CF cells. The size increased by about 7K.

Here is the formula you should use (for example in cell M4):

"Formula is:

 =CELL("type",M4)="b"

Copy the cell and you will copy the conditional formtting along with it.

 

[sdraper]

Ypu mentioned you want to only have the unlocked cells be selectable. try this
Sub Autpen()
Worksheets(1).EnableSelection = xlUnlockedCells
End Sub
Note that this will only work when the sheet is protected
sdraper