Bug in Conditional Formatting - Manage Rule

[grantrob]

This is a really strange bug. I am using Office 365 (latest version) Excel and have a series of worksheets set up. One worksheet has over 140 lines and I have Consitional Formatting set up for each line, To save time, I have copied the formatting across from a seperate worksheet to copy across the Conditional Formatting rules. However, as the worksheet I am trying to fix has more lines than what I have copied from, I am having to go in and mnually edit each of the subsequent lines using ConditionalFormatting - Manage Rules. Whilst this is laborious, it works for about 5 lines and then the edit string locks me out so I am unable to edit and have to save my work, shuit down and restart Excel - then everythig continues to work for the next 4 or 5 lines. I then have to repeat the same process just editinting 5 lines at a time. Any ideas?

 

[RoryA]

I have Consitional Formatting set up for each line

Do you really have different rules for every row?

 

[grantrob]

This is a really strange bug. I am using Office 365 (latest version) Excel and have a series of worksheets set up. One worksheet has over 140 lines and I have Consitional Formatting set up for each line, To save time, I have copied the formatting across from a seperate worksheet to copy across the Conditional Formatting rules. However, as the worksheet I am trying to fix has more lines than what I have copied from, I am having to go in and mnually edit each of the subsequent lines using ConditionalFormatting - Manage Rules. Whilst this is laborious, it works for about 5 lines and then the edit string locks me out so I am unable to edit and have to save my work, shuit down and restart Excel - then everythig continues to work for the next 4 or 5 lines. I then have to repeat the same process just editinting 5 lines at a time. Any ideas?

View attachment 111208

View attachment 111207

Further update: I have narrowed down the problem and definitely appears to be a bug in Excel 365.

It turns out that the problem ONLY occurs if I am editing the Conditional Formatting Rule for a particular row which is near the bottom of my screen. If I then save my edit and close the dialogue box, if I then use my mouse wheel to scroll down to the next set of rows and then select my next row to edit my next rule, then the problem occurs. However if I just continue down the worksheet without using my mouse to scroll, then everything continues on perfectly. Maybe I should report this to Microsoft?

 

[RoryA]

Maybe I should report this to Microsoft?

Yes, that does sound like a bug.

 

[Alex Blakenburg]

You didn't reply to Rory's post #2, it seems a very unusual way to apply conditional formatting.
Is there any reason your conditional formatting isn't applied to the whole range at once using Relative row referencing ?

 

[grantrob]

You didn't reply to Rory's post #2, it seems a very unusual way to apply conditional formatting.
Is there any reason your conditional formatting isn't applied to the whole range at once using Relative row referencing ?

View attachment 111257

Yes - I can’t apply to a whole range because I only want specific cells in the one row to be highlighted if N/A each time

 

[Alex Blakenburg]

Doesn't the logic apply to all cells in specific columns ?

 

[grantrob]

Doesn't the logic apply to all cells in specific columns ?

I tried but doesn’t do what I need

 

[Alex Blakenburg]

Can you give us 2 or 3 examples in the same column, with the information like the screen shot I have above.
ie What cell is being formatted, what the rule is in the conditional formatting
Why you think the same rule can't be applied to the 2 or 3 examples you have ?

Another option is to share you worksheet with the conditional formattinig in it via dropbox, google drive etc, you can remove most of the data since it is more about the conditional formatting.

 

[grantrob]

Can you give us 2 or 3 examples in the same column, with the information like the screen shot I have above.
ie What cell is being formatted, what the rule is in the conditional formatting
Why you think the same rule can't be applied to the 2 or 3 examples you have ?

Another option is to share you worksheet with the conditional formattinig in it via dropbox, google drive etc, you can remove most of the data since it is more about the conditional formatting.

Thank you for the offer of support but the updates have already been completed (once I realised what was causing the problem then it was easy to finish). My original post was more to highlight a possible technical bug within Excel which still appears to be the case. I will report directly to MS