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.

 

[etaf]

it may be useful to see the rules and selection
is this using VBA - or the NEW check boxes , as you show 2021 and I dont think checkbox was vailable as an inset ?

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

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

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[Marvo]

The formula I entered and want in conditional formatting is

=RIGHT(A1,1)="✔" applies to =$A$1:$P$560

As soon as I make any changes like adding a tick or adding/deleting a cell that formula automatically changes to

=RIGHT(A1,1)="✔" applies to =$A$1:$Z$450

I just want to know why and is there a way to stop it changing?

Maybe I could make =$A$1:$P$560 a named range? Would that work within conditional formatting?

 

[etaf]

what font are you using to get the tick , and why the right ?

 

[Marvo]

I'm solving different problems. Each has a number 1 to 50000. I just added a tick to the end of the number to identify which problems have been solved and I was then trying to use condition formatting to turn the cell green to show it completed. However when you add a tick to another number, or add, copy or delete a cell (Basically make any changes) the conditional formatting changes. Very frustrating. I tried a named range, I tried a macro, just cant solve the problem and it looks like through googling like many people have had the same problem. Maybe what I want can't be done so perhaps I can find a different avenue in order to easily identify solved problems.

Thanks for trying to help.

 

[etaf]

i still dont know how a tick is being adding - using a font or symbol

if you insert cells and delete cells I know that changes the range selected

without an example as requested in post 2 , i'm unable to help further here

 

[Marvo]

I've changed the tick to an asterisk. Hasn't made any difference. Conditional formatting insists of reverting to
=$A$1:$Z$450

Why Column Z? There's nothing in any column after Q. Why Row 450, the data goes down to 547.

I give up. I just have to keep resetting the "applies to" rule.

Maybe VBA to change the
=$A$1:$Z$450
to
=$A$1:$Q$547

with an update button. No idea if that would work.

Again, thanks for taking the time.

 

[Peter_SSs]

I had a condition that in E35:E550,

=RIGHT(A1,1)="✔" applies to =$A$1:$P$560

I am confused about the range.

 

[Marvo]

Mr Range at present is A1:Q547. Within that range I wanted any cell with a number with an asterisk at the end to fill green. Example: 4567*

Conditional formatting changes this to A1:Z450 whenever any alterations are made. Even if I cant get this sorted, I'd love to know why it reverts to this. It seems random.

Column E is the longest with data. It is this column that fails when amendments are made anywhere within the range, with cells from E451:E547 changing to no-fill.

Incidentally I use the asterisk in order to find the biggest gap between completed numbers. If I could do that with colour, I could manually change each completed task to green.

Example: =MAX(FREQUENCY(IF(RIGHT(E2:E547,1)<>"*",ROW(E2:E547)),IF(RIGHT(E2:E547,1)="*",ROW(E2:E547))))

Note: I'm currently in Australia so now off to bed. If there are any further suggestions I can respond in about 8 hours.

 

[Peter_SSs]

Even if I cant get this sorted, I'd love to know why it reverts to this.

I have been unable to reproduce what you are describing. If you would like further investigation, would you be able to remove or disguise any sensitive data and upload the workbook to DropBox or OneDrive or Google Drive and provide here a publicly shared link so we can take a look at the specific workbook. If you can do that, before uploading make sure that the conditional formatting is in place and that it "Applies to" the correct range (A1:Q547 I think as per post #9). Then when we open the workbook and add an asterisk we can see if it changes the "Applies to" range for us too.