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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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?
 
Upvote 0

Forum statistics

Threads
1,218,795
Messages
6,144,527
Members
450,550
Latest member
Melanie_Annies

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
Back
Top