Retaining conditional format in a row
Working on a project that highlights a date when it is near bolds it when its due and strikes it when its past.
So far it seems to work just fine... except when you're adding an additional row or cut/paste the row. the conditional formatting includes a parameter formula within the column rather than staying within the row.
I am hoping to figure out a way to lock down the conditional formatting, (or completing the task in a different way) so a user can cut/past and still have the rules apply.
I'm not sure what you had in mind when you set these rules up. For example this rule:
=$AA$9="strike"
applies to D9:D10. Because of absolute addressing, both rows will refer to $AA$9. I don't think that's what you intend.
Write one rule for the entire column D (I did this for you). Then you need one rule for each format. That should be immune to inserting or deleting rows, but if you paste anything into column D it will wipe out the conditional formatting for that cell unless you Paste Values. That will cause the Applies To range to be broken up. That just how it works--when you do a Paste, it pastes everything.
You could do something a bit more complicated with VBA, which would be absolutely immune to any shenanigans on the sheet.
Jeff
| | |·| |·| |·| |·| | |:| | |·| |·|
Read the rules
Use code tags to [code]enclose your code![/code]
Jeff, My apologies, I had thought my reply had been submitted, but it looks like i had some difficulties on my end.
I looked over your modifications, and it seems like it works although I am a little confused how it works? for example, Cell D13 Conditional Formatting (red no strike) refers to cells AG1<AE1, which is the header? yet somehow this code is functional. however D14 has the same formatting, yet does not work
My thoughts were to create a sort of flag system that would tell me when am getting close to a date with these formulas. I am trying to have a 6 day heads up notice for these guys.
Thanks for the Help!
Conditional formatting rules are written as though they apply to the upper-left cell in the Applies To range. In this case that range is D:D so the rules are written as if they were for D1. Therefore references to other cells in the same row also look like row 1. But they are applied to the actual row with the data to be formatted. So for formatting D14, the rule is applied to AG14 and AE14. The idea is similar to relative references in worksheet formulas.
It looks to me like D14 works the same as the other rows.
Wow, i see now, this is really awesome, i think this solves my problems Thank you!
Glad to help!
If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."
If a member helped you solve your problem, consider adding to their reputation by clicking addreputationiconsmall.jpg below their name.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks