Conditional Formatting Adds Itself When I tab to the Next Cell

[ozziewendy]

I have a spreadsheet that has conditional formatting on 2 adjacent cells (G5 & H5). The formatting uses a formula to highlight the cells if the sum of their values is higher than the value in a third cell (D5). The conditional formatting works fine.

However, having entered values into each of these cells I then tab to I5 and bizarrely the conditional formatting suddenly appears there too! This happens whether or not the condition is triggered, so even if G5 & H5 are not > D5, the conditional formatting rule is updated to apply to cell I5 when I tab into it from H5.

Why is this happening and how do I stop it?

 

[Khalid NGO]

Hi,

Can you post a sample file?

Regards,

 

[ozziewendy]

ok, the spreadsheet is only in its infancy of development. Go to the sheet 'Events Programs etc' and check the conditional formatting rules, you will see that there is a format over J5:K24. Now enter data into row 6 (you only need to start at the total no of attendees). Once you have entered data into cell K6 (no of ATSI attendees) and tab to L6, check the conditional formatting again and it has now been extended to cover L6. At least, that's what happens on my PC! My version of Excel is 'Microsoft Office Home and Student 2016' on Windows 10.

Wendy

 

[Khalid NGO]

check the conditional formatting again and it has now been extended to cover L6.

Hi Wendy,

I haven't faced that problem.


Regards,

 

[ozziewendy]

Hi Wendy,

I haven't faced that problem.

View attachment 36091
Regards,

Khalid, It happens on my work computer as well, which is running Office Professional 2013, but I found that it didn't happen if I don't enter values in each of the cells as I tab across the row after entering the total. I think if you enter a total, then tab across and enter a value in each of the cells you will find it happens.

Wendy

 

[Khalid NGO]

Hi Wendy,

Can you indicate all the steps with cells references?

Regards,

 

[ozziewendy]

Hi Wendy,

Can you indicate all the steps with cells references?

Regards,

Khalid,

Start in cell A6 and select/enter data in all cells up to and including L6. Use <tab> to navigate from cell to cell across the row. I have included a file with a couple of screen prints showing the validation rules before and after this process. For me it is totally repeatable, and happens on both my home and work computers running quite different versions of Excel.

Wendy

 

[Khalid NGO]

Hi Wendy,
Thanks for the detailed reply.

Again nothing seems to be a problem at my end

Here is the snap, Applies to range didn't changed after said process:


I am not sure why this happening at your end, I suggest to wait someone to respond.

Regards,

 

[bosco_yip]

Try to select the check box of "Stop IF True" of the Conditional Formatting as follow :

Details by press F1 in search for "conditional formatting stop if true"

Regards

 

[ozziewendy]

By chance I discovered what was causing this for me. In Options / Advanced there is an option 'Extend data range formats and formulas'. I unticked this and the problem went away.

Thanks for trying folks.