Conditional Formatting

the_deejus

New Member
Joined
May 15, 2006
Messages
36
Hi,
Just wondering, is it possible to 'lock' the conditional formatting in place so that when one has a column and someone else uses "sort ascending" A->Z button, the formatting behind the text doesnt follow it all over the place.
I have conditional formatting in some rows/columns which when a date is placed in column'L' the whole row turns from red to black. (dont remember the formula) but when people sort the column, I've found the formatting behind the text also moves up or down the column. This means that adding a date in (lets say) L8 changes the row to black in row 6 instead of row 8.

Can the formulas be locked in Excel so every thing says where it is suppose to?
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
Not sure I understand.

If I set A1's conditional formatting to formula is =$L$1<>"" and apply a red background and sort column L (so that what used to be L1 is now L3), the conditional formatting in A1 still references (the new) L1 (instead of the old L1/new L3) which I think it what you'd like to happen. Maybe I'm misinterpreting your question?
 

Forum statistics

Threads
1,137,206
Messages
5,680,191
Members
419,887
Latest member
Vasokir

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top