Conditional Formatting Problem

[Kayla22R]

I created this sheet so my instructors can keep track of the activity records for the clients and I use conditional formatting for each line so that certain cells turn a certain color when they mark something done. I do the same exact formula's for each line, and as in my picture, I use multiple formulas on each line. For example, line 7 for cells H-I, I have it be applied to =$H$7:$I$7. This all works perfectly, so I am not having the issue with the formula's working, the problem I am having is that certain cell formulas keep jumping to another cell formula. Example of this is in my first picture, the part that I circled green is exactly how it is supposed to be for each line, but if you see in the red, line 7 formulas changed from $H$7, $I$8 when it should be $H$7:I$7. Sometimes a cell like I8 will have it's formulas completely gone and moved to another "I" cell because that cell got added onto cell I7. I don't know why this keeps happening. It has been happening for a few months now and I monitor this sheet everyday for these mistakes and have to constantly keep fixing them and I am tired of it. I thought maybe the instructors kept accidently dragging a cell to another cell so as you see in the 2nd picture, I went to options and took off "Enable fill handle" so that they can't drag cells anymore. That didn't fix it. So then as you see in pic 3, I protected a lot of specific cells (left the ones that they have to use drop down menus with or write in unlocked) and then I locked the sheet and no one knows the password, just me so I know no one has been going in and changing things. When I lock the sheet, the only box I have selected is "select unlocked cells" and they can still do what they need on the sheet, but this still doesn't work. The last picture is exactly how each line should look for cells G to I or some H to I. I used line 7 for H - I to show you exactly how I want it to stay. I am really tired of this keep happening, and would really appreciate it if someone could help me stop this from happening.

 

[zero269]

I created this sheet so my instructors can keep track of the activity records for the clients and I use conditional formatting for each line so that certain cells turn a certain color when they mark something done. I do the same exact formula's for each line, and as in my picture, I use multiple formulas on each line. For example, line 7 for cells H-I, I have it be applied to =$H$7:$I$7. This all works perfectly, so I am not having the issue with the formula's working, the problem I am having is that certain cell formulas keep jumping to another cell formula. Example of this is in my first picture, the part that I circled green is exactly how it is supposed to be for each line, but if you see in the red, line 7 formulas changed from $H$7, $I$8 when it should be $H$7:I$7. Sometimes a cell like I8 will have it's formulas completely gone and moved to another "I" cell because that cell got added onto cell I7. I don't know why this keeps happening. It has been happening for a few months now and I monitor this sheet everyday for these mistakes and have to constantly keep fixing them and I am tired of it. I thought maybe the instructors kept accidently dragging a cell to another cell so as you see in the 2nd picture, I went to options and took off "Enable fill handle" so that they can't drag cells anymore. That didn't fix it. So then as you see in pic 3, I protected a lot of specific cells (left the ones that they have to use drop down menus with or write in unlocked) and then I locked the sheet and no one knows the password, just me so I know no one has been going in and changing things. When I lock the sheet, the only box I have selected is "select unlocked cells" and they can still do what they need on the sheet, but this still doesn't work. The last picture is exactly how each line should look for cells G to I or some H to I. I used line 7 for H - I to show you exactly how I want it to stay. I am really tired of this keep happening, and would really appreciate it if someone could help me stop this from happening.

Conditional Formatting is finicky to say the least. I would start with applying the Range to the column and not Rows. I've had issues with CFRs being broken up as well when deleting a Table Row. Some times it happened, most often it didn't. This is why I try to avoid CFR's whenever possible.

I would also recommend using a Formatted Table so you don't have to highlight ranges of emtpy cells, that you later want to change when used, vs. highlighted only when it meets an important criteria that you want to bring attention to... vs. ignore.

Without sample data, it makes it harder to advise.
Try using this forums Excel add-in so you can select a range of cells and create a Mini Sheet that you can paste in this forum.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

Best of luck and welcome to the forum...