How to keep conditional formatting formulas from automatically changing

[Marvo]

Hi. I have the following in conditional formatting.

Under Format values where this value is true I have
-RIGHT(A1:AZ250,1)="✔"
Under applies to I have
=$A$1:$AZ$250
This works fine (formats the cells in green) however as soon as you add/delete/move a cell within the range the formatting goes crazy, even colouring some cells green which don't contain a ✔.

Is there a way to stop the CF automatically changing?

I read elsewhere you can use INDIRECT and/or a named range in conditional formatting to stop it but haven't been able to make that work either.

Thank you.

 

[Kaper]

Try to select all range A1:AZ250, remove old formatting and use just:
=RIGHT(A1,1)="✔"

This shall keep the proper formatting of a cell when moved, deleted and so on.

But this may be not enough as if we imagine you moved cell C7 to F12. or just inserted cell in C7 position. Then in C7 you no longer have conditional formatting and new value (even if it ends with "checked") will not "fire" the conditional formatting in this cell.

The above mentioned issue could be solved with event handler for Change event instead of just conditional formatting. but ... this requires file to be macro-enabled. and the part of "uncoloring" cells which no longer meet the formatting condition can mean interfering with other formatting, manually introduced in cells.

 

[Marvo]

Thank you. That works in that if you delete a cell the colour keeps to the correct cells. However it changes the applies to bit so if you put the cell back there is no formatting (like you said). Its annoying, I don't want the range the formula applies to to change at all. I did read that if you make the range "named" then you can use that in CF and it wouldn't change but I cant get that to work.

 

[Kaper]

I checked named ranges, and they are changed into the real ranges in CF definition, so nothing better here.

You may right-click on tab with sheet name, click on Display Code, paste the following in the window which shall be visible:

Private Sub Worksheet_Change(ByVal Target As Range)
  Cells.FormatConditions(1).ModifyAppliesToRange Range("A1:AZ250")
End Sub

close VBA editor and retun to your workbook. Save it in macro enabled format (xlsm, xlsb). Close and reopem making sure macros are enabled.

After any change in a sheet original applies to setting is restored.
(I used FormatConditions(1) assuming there is only this conditional formatting rule - if there are more, a proper number shall be used instead of 1).

 

[Marvo]

That's fabulous. I didn't know about the right click shortcut to VBA. Thank you very much.

 

[Kaper]

I'm glad that it worked the thread.
As for right-click on a Sheet tab - this is in particular useful if you want to place some code in a Worksheet module.
If the code is be inserted into standard module, I'd probably open VBA editor standard way - with Alt+F11