It checks to see if the changed address (passed in the Target variable) is part of the original cells that contained conditional formats. The macro is executed every time there is a change in the worksheet. (If you changing conditional formatting at a later time, you can use the Immediate window trick again and simply change the line in the above macro.) The key here is to make sure that the cCFAddress constant is set equal to whatever was returned when you saw the addresses in the Immediate window. If Application.Intersect(Target, r) IsNot Nothing Then
COPY CONDITIONAL FORMATTING EXCEL 2016 CODE
Next, add the following macro to the ThisWorksheet code module: ? Cells.SpecialCells(xlCellTypeAllFormatConditions).AddressĪssuming you have your conditional formatting all set up, this should return a line-perhaps a long line-that shows the addresses of the cells and ranges that use conditional formatting. Next, go into the Visual Basic Editor and, in the Immediate window, enter the following:
For instance, let's say that the macro you record is called something short and sexy like SetCondFormat.
If you can record a macro that applies the conditional formatting to the cells, you could create some additional macros that apply that recorded macro, as needed. (You just don't want them to mess up the conditional formatting.) This may be a bit drastic for your users, however, as you may want them to make changes. Instead, they should use the paste options to paste anything (or everything) except formatting.Īnother option, of course, is to protect the worksheet so that the user cannot copy and paste anything. The answer, then, is to tell users not to do a standard copy and paste. This means the target cells will have the formatting of the source cells, not the formatting of the target cells (including any conditional formatting that may have been in the target cells). A standard copy and paste (or a drag and drop edit) copies everything, including formatting. This happens because conditional formatting is considered just that-formatting. He wonders about the best way to prevent this from happening. However, the conditional formatting keeps getting messed up when users copy and paste information or when they use drag and drop to edit the worksheet. Kees has a worksheet that uses conditional formatting extensively.
0 kommentar(er)
