Maintaining a rigid conditional format

[Marvo]

Is there anyway of making conditional format stick to the original selection of cells?

I had a condition that in E35:E550, should a tick be placed at the right of a number, the cell would fill in green. It worked when initially instigated but as soon as I added a tick to another number (to show it was completed) conditional formatting changed to A1:Z450. I have no data after column P so why it should go to that selection of cells I have no idea. Its driving me nuts.

Many thanks.

 

[Marvo]

Your wish is my command

Type anything anywhere, the conditional format will change.

https://docs.google.com/spreadsheets/d/1iCazhUNq-Wgwi6i0tJlj4_RHon2NBKmP/edit?gid=214190965#gid=214190965

 

[Peter_SSs]

Your wish is my command

The link says "You need access"

 

[Marvo]

Okay, I'll try again. Not sure how it works.

 

[Marvo]

I've just ticked a box "anyone with the link". Does it work now?

 

[Peter_SSs]

Does it work now?

No. Having ticked that box there is possibly a different link now.

 

[Marvo]

No. Just did it again, same link. I'll have another look.

 

[Marvo]

https://docs.google.com/spreadsheets/d/1iCazhUNq-Wgwi6i0tJlj4_RHon2NBKmP/edit?usp=drive_link&ouid=106250786241344075445&rtpof=true&sd=true

Try that one. Its on the HARD page.

 

[Peter_SSs]

Sorry, it still says "You need access"

 

[Peter_SSs]

OK, I have got access, thanks.

The issue is that you have vba code in the workbook that is set to change the conditional formatting range to A1:Z450 whenever any change is made in that worksheet. The code is in the 'Hard' worksheet's vba module as shown below. Perhaps try deleting that code unless you have it there for some particular reason.

 

[Marvo]

Well that's that solved. Thank you Peter, sorry to have been so much trouble, god knows how long that VBA has ben there, the workbook dates back several years.