Copying Conditional Formatting when cells are/aren't protected
Hi,
I know there are many issues with conditional formatting when users cut/paste or insert rows. What I'm trying to do is copy only the conditional formatting from a hidden sheet ("ParticipationLevelValues") to a target sheet the users are using. All the cells on the hidden sheet are unlocked as the default.
When the user saves the file is when my code executes. This way when they open the file the next time, all the conditional formatting rules have been reset. The spreadsheet is 78 columns wide and might run 12,000 rows deep.
When the user hits a "SAVE" button, the macro unprotects the sheet and checks the validity of the data entries before resetting the conditional formatting rules. After this, then the macro protects the sheet and then saves the file.
The above code sort of meets my needs as the conditional formatting plus all the other formatting gets copied. The problem is some of the cells in the target sheet are locked via Worksheet_Change(ByVal Target As Range) during data entry depending on the value of the cell (if the cell is "Yes", it gets locked; otherwise, it is unlocked).
If a cell is locked and I use the code above, the conditional formatting that gets copied over is correct, but the previously locked cells in the target now become unlocked. I do not want the format cell protection to be changed.
Is there any way if a cell is locked on the target sheet, to apply the conditional formatting and still leave it locked. The same goes if a cell is unlocked on the target sheet, to apply the conditional formatting and still leave it unlocked.
Is there a way to copy/paste just the conditional formatting rules?
I thank you in advance for any assistance or suggestions you have.
Re: Copying Conditional Formatting when cells are/aren't protected
One option is to include the protect/unprotect in the code while unlocking the cells the user needs to access. Furthermore, the conditional formatting can be added directly through code without the need for copying cells. Here is an example of both:
PHP Code:
Sub Protect_Unprotect_AddConditionalFormating() Dim c1 As FormatCondition, rng As Range ActiveSheet.Unprotect Set rng = ActiveSheet.Range("K2:K100") Set c1 = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=AND(K2>0,L2=""y"")") c1.Interior.Color = 11854022 ActiveSheet.Range("A2:T149").Locked = False ActiveSheet.Protect AllowFormattingColumns:=True, AllowFormattingRows:=True End Sub
Re: Copying Conditional Formatting when cells are/aren't protected
I should have made it clear that when the user saves the file is when my code executes. This way when they open the file the next time, all the conditional formatting rules have been reset. The spreadsheet is 78 columns wide and might run 12,000 rows deep.
When the user hits a "SAVE" button, the macro unlocks the sheet and checks the validity of the data entries before resetting the conditional formatting rules. After this, then the macro locks the sheet and then saves the file. I presumed the most efficient method would be to copy/paste conditional formatting from the source sheet than to hardcode the conditional formatting for each column as you have suggested.
Is there a way to copy/paste just the conditional formatting rules?
Re: Copying Conditional Formatting when cells are/aren't protected
Thank you very much for your assistance as it got me on a better path.
However, now I get a "Run-time error '1004': Application-defined or object-defined error" for some reason in the following code at the "MyRange.FormatConditions.Delete" line and I don't understand why.
PHP Code:
Public Sub FormatRow() Dim MyRange As Range
Set MyRange = ActiveSheet.Range("A9:BG12500") MyRange.FormatConditions.Delete
'First rule highlights the active cell in pink while the second rule highlight's the active cell's row and column in blue. 'First CF Rule MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(CELL(""col"")=CELL(""col"",A9),CELL(""row"")=CELL(""row"",A9))" MyRange.FormatConditions(1).Interior.Color = RGB(255, 204, 255) 'Light pink MyRange.FormatConditions(1).StopIfTrue = False
Re: Copying Conditional Formatting when cells are/aren't protected
Even when I try that suggested piece of code to remove all the format conditioning on the sheet, I still get the "Run-time error '1004': Application-defined or object-defined error" message.
PROBLEM SOLVED: I needed to unprotect the worksheet before running the code to avoid the 1004 error. Duh! Somehow the piece of code to unprotect was commented out. I couldn't see the trees through the forest...my fault. My last piece of code works if I unprotect it at the start and then protect it at the end of the subroutine.
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks