Conditional Format

[indub]

Hello,

I have an excel workbook with multiple worksheets and each sheet has it's own conditional format rules. Those rules were made without vba. I'm just curious if there's a way to prevent changes to those conditional format rules on each worksheet using vba?

The problem is when I copy and paste a row (using ctrl-v), it'll copy the conditional format rule. For example, using a conditional format with formula INDIRECT("A"&ROW())="x" for range $A$1:$S$10000 will make all cells within a row fill a certain color if "x" is entered in Column A for said row in that range. But, if I for example ctrl+v values from row 1 to row 2, the format rule will automatically change the range and create a copy of the rule to that specific row, i.e. $A$1:$S$10000 may become two rules for the same format: 1)$A$1:$S$1,$A$3:$S$10000 and 2)$A$2:$S$2. Eventually there could be hundreds or thousands of the same rule due to ctrl+v. I realize I can easily get around this by only pasting formulas or values but not everyone who using this spreadsheet will do that, they'll just use ctrl+v. Is there a simple vba procedure to enter for each sheet just to prevent changes to the conditional format rules so users can still use ctrl+v? None of the sheets are protected.

I haven't looked into writing the conditional format rules for each sheet in vba instead. If that would be easier than what I ask for above, let me know and I'll look into that. There's a lot of conditional format rules on each sheet though so I think using vba for the conditional format rules would be complicated.

Thanks in advance!

[Fettertiger]

Hi,

you could use the following macro to ensure that a paste is replaced by a "paste values". This will ensure that all orginal formating (including conditional formating) is kept.

Please Login or Register  to view this content.

The macro needs to be added into the worksheet in Visual Basic editor - NOT in the normal Code module:
Use the Visual Basic Editor -> in the Project Explorer, double click the appropriate sheet (under 'Microsoft Excel Objects' which is under the VBAProject/name of your workbook) -> in the Code window, select "Worksheet" from the left-side "General" drop-down menu and then select "SelectionChange" from the right-side "Declarations" drop-down menu. Paste the above macro into the "selectionCHange" sub.

If you want to "protect" multiple sheets, you will need to paste the macro to each of the worksheets. Make sure to adjust the "Sheet 1" with the proper sheet name.

Let me know if this works as expected.

Theo

[indub]

That's almost exactly what I was looking for! Thanks! I only needed to change Target.Value to Target.Formula, and the sheet name of course. Just curious, why does it block undo/redo? Is there a way to add that functionality in or does the errorhandler section block it?.

While we're discussing formats, I'd also like to protect some sheets further with 'sheet.protect "" structure:= windows:= but setting the structures to TRUE makes data validation drop-down lists unusable. Is there a way to protect the structure but allow data validation from other sheets?

Thanks again!

[indub]

Well unfortunately it looks as though something isn't working. I've tried a few times now on copies of the same workbook. Each time any worksheet I open will immediately go back to Sheet1 aka "Snapshot". I just tried it again on another backup copy of the workbook and it did weird things. When I switched back from the vb window to the excel worksheet after entering and modifying the code, the worksheet flickered dark/light repeatedly, when the sheet stopped flickering the whole workbook locked up like it was protected, but I could use the ribbon. I closed the workbook then re-opened and the original issue happened again, any tab clicked on automatically changes back to Sheet1. I think other sheets are going back to Sheet1 (aka "Snapshot") because I have a sub in ThisWorkbook to activate Sheet1 when the workbook is opened. However, if I delete the selectionchange sub from every page then its okay, working again.