monirg
Well-known Member
- Joined
- Jan 11, 2005
- Messages
- 629
Hi. Please provide guidance to the following question.
1. "Values" and "Formats" are pasted to Cells D20 and D25 via an optimization macro (2 successive PasteSpecial; XlValues & XlFormats).
2. The Conditional Format in D20 compares the pasted value with the value in Cell H12 on the same sheet, and determines whether to apply the Pasted "Format" or the "Conditional Format" format.
3. This part works fine. The following part is what I'm having difficulty with.
4. I would like to add / include a format to the same cell D20 Coditional Format to respond differently if the change to D20's value is a user change (not the optimization macro's change).
5. Let us assume that running the macro would return to Cell D20:
.......... a numerical value, and
.......... Blue interior (could be any colour depending on the optimization)
And in Cell D20, I've the following Conditional Format:
..Condition 1........ Formula Is:: = ISBLANK($D$25)
............................ format :: Yellow interior (yields yellow if the macro was not run, and the changes are user changes)
..Condition 2........ Formula Is:: = AND($D$20 <> $H$12, ISNUMBER($D$25))
............................ format :: Gray interior (yields gray if condition is True, after running the macro)
..Condition 3........ Formula Is:: = AND($D$20 = $H$12, ISNUMBER($D$25))
............................ format :: No Format Set (yields macro's Blue interior if condition is True after running the macro)
5. The question is: How to modify the above Conditional Format so that my manual changes to cell D20, if any, after running the optimization macro may produce Red (or even Gray) interior, and remains so unless I run the macro again ??
6. Adding a w/s Change event would interfer with the optimization macro's PasteSpecial Xlformats (1. above).
Any suggestions would be greatly appreciated. Thank you.
1. "Values" and "Formats" are pasted to Cells D20 and D25 via an optimization macro (2 successive PasteSpecial; XlValues & XlFormats).
2. The Conditional Format in D20 compares the pasted value with the value in Cell H12 on the same sheet, and determines whether to apply the Pasted "Format" or the "Conditional Format" format.
3. This part works fine. The following part is what I'm having difficulty with.
4. I would like to add / include a format to the same cell D20 Coditional Format to respond differently if the change to D20's value is a user change (not the optimization macro's change).
5. Let us assume that running the macro would return to Cell D20:
.......... a numerical value, and
.......... Blue interior (could be any colour depending on the optimization)
And in Cell D20, I've the following Conditional Format:
..Condition 1........ Formula Is:: = ISBLANK($D$25)
............................ format :: Yellow interior (yields yellow if the macro was not run, and the changes are user changes)
..Condition 2........ Formula Is:: = AND($D$20 <> $H$12, ISNUMBER($D$25))
............................ format :: Gray interior (yields gray if condition is True, after running the macro)
..Condition 3........ Formula Is:: = AND($D$20 = $H$12, ISNUMBER($D$25))
............................ format :: No Format Set (yields macro's Blue interior if condition is True after running the macro)
5. The question is: How to modify the above Conditional Format so that my manual changes to cell D20, if any, after running the optimization macro may produce Red (or even Gray) interior, and remains so unless I run the macro again ??
6. Adding a w/s Change event would interfer with the optimization macro's PasteSpecial Xlformats (1. above).
Any suggestions would be greatly appreciated. Thank you.