Conditional Format not working in a Protected Table

**antexity** · 09-03-2015, 03:04 PM

See attached.

I have a table with a Macro (see button " Insert new row" ref Macro "Copyrow") that protects the table and inserts a new row. This part works great. The issue I am having is getting the conditional format to highlight duplicates in all of column C. If I enter in duplicated numbers outside the table it the conditional format works. Even works when the sheet is unprotected IN the table. The second I hit the button to insert a row and enter a duplicated number the conditional formatting stops.

Any ideas on how to get this working?

Thank you so much!
Sample RPT.xlsm

**TMS** · 09-03-2015, 03:51 PM

Please Login or Register  to view this content.

Regards, TMS

**TMS** · 09-03-2015, 03:53 PM

I should have said ... I don't know why you are copying a blank row 3. It is unnecessary. Adding rows to a Structured Table will automatically copy formulae, formatting, Conditional Formatting and Data Validation.

Regards, TMS

**jaslake** · 09-03-2015, 04:17 PM

Hi antexity

TMS's Code is spot on, however, you also need to check your Table Size. In the Sample File it's sized incorrectly. Also, the Conditional Formatting Rule should be revised.

antexity.jpg

**skywriter** · 09-03-2015, 04:24 PM

Your profile shows you have Excel 2010 like I do, so to elaborate a little on what Trevor is saying. If you select cell A3 of the sample file you included you will see that you immediately get a Table Tools tab on the ribbon. Now select cell A6 or the file you attached and you see the tab goes away. That is telling you that the cell you selected is not a part of the table, this is the root of your problem. Trevor's code is addressing the table structure and adding a row to the table, not just the worksheet.

**antexity** · 09-03-2015, 04:27 PM

This is perfect TMS! Another feature that would be nice, is there a way to lock the data entered in the previous rows from being edited with having the current row able to edit. The user would lose the functionality to edit old row once the insert next row button is clicked.

Thanks again

Antexity

**antexity** · 09-03-2015, 04:32 PM

Thanks jaslake and skywriter for your observations, I have fixed those issues. Thanks!

**antexity** · 09-03-2015, 04:36 PM

Also to add to my latest post #6, is there a way to set a password when protecting and prevent from the end-user seeing the password. IE. I click the file and say protect sheet and use a password. Then I start adding rows and since in the macro, the logic is to unlock and lock the sheet, after the first attempt to unlock (and entering the password to unlock) and then lock I lose the password feature. If there is a way to address this and the solution is to hard code the password in the Macro logic, is there a way to prevent the end user from going to the developer tab > macro or view code to see the password?

Thanks
Antexity

**skywriter** · 09-03-2015, 05:18 PM

This seems to work. Change the password to whatever you want. If you set the worksheet protection manually, meaning how you want it. Select locked or unlocked cells etc., then when you run this you should find the new row unlocked and the other cells locked with the settings you specified.

Good Luck.

Please Login or Register  to view this content.

is there a way to prevent the end user from going to the developer tab > macro or view code to see the password?

In the Visual Basic Editor > Tools > VBA Project Properties > Protection Tab, the rest is self explanatory.

**TMS** · 09-03-2015, 05:54 PM

Yes, I probably should have mentioned that I deleted a lot of Conditional Format tests.

I don't think the CF should be applied to the whole of column C. The CF will be managed by Excel as it relates to a Structured Table.

I think this combines the various aspects so far proposed.

Please Login or Register  to view this content.

See the attached updated example.

Regards, TMS

**antexity** · 09-03-2015, 10:50 PM

All you guys nailed my requirements. I really appreciate your time!. I have added reputations to all if you.

Thank you!
Antexity

**jaslake** · 09-03-2015, 11:09 PM

You're welcome...glad I could help. Thanks for the Rep.

**skywriter** · 09-04-2015, 02:19 AM

Thanks for the rep. points.

**TMS** · 09-04-2015, 03:14 AM

You're welcome. Thanks for the rep.

Conditional Format not working in a Protected Table

LinkBack

Thread Tools

Rate This Thread

Display

Conditional Format not working in a Protected Table

Re: Conditional Format not working in a Protected Table

Re: Conditional Format not working in a Protected Table

Re: Conditional Format not working in a Protected Table

Re: Conditional Format not working in a Protected Table

Re: Conditional Format not working in a Protected Table

Re: Conditional Format not working in a Protected Table

Re: Conditional Format not working in a Protected Table

Re: Conditional Format not working in a Protected Table

Re: Conditional Format not working in a Protected Table

Re: Conditional Format not working in a Protected Table

Re: Conditional Format not working in a Protected Table

Re: Conditional Format not working in a Protected Table

Re: Conditional Format not working in a Protected Table

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Conditional Format not working as expected

[SOLVED] Conditional Format not working

conditional format not working

conditional format not working

Keep format of data table after insert a new row on a protected sheet

Conditional Format not working

[SOLVED] Conditional Format Not Working

Bookmarks

Bookmarks

Posting Permissions