Conditional formatting does not follow value when list is sorted

[rtbrd]

I have a set of dates and times in a schedule that are compared to holidays
and conditionally formatted (changes text color) if the date is a holiday.
There are a number of these schedules in the entire sheet.

I copy all the dates below the schedules and then sort them by date and
time. Before sorting the dates reflect the correct formatting (holidays
remain blue). After sorting the holidays no longer have the correct
formatting. The values in the cells are now in sequential order as they
should be but the fromatting stays in its original cell and does not move
with the cell value. Now non-holidays are formatted in blue.

My conditional format is: Formula is =$Q$7=TRUE
where cell Q7 compares the date to the holidays.

Is there any way to have the conditional formatting follw the cell value?

 

[David Biddulph]

Might the problem be that you've used absolute, rather than relative,
addressing?
What happens if you change your CF formula from =$Q$7=TRUE to =Q7=TRUE, or
even just to =Q7 ?
It also isn't clear how you've got your data laid out, and whether you
sorted your intermediate cells (like Q7) with the corresponding data.
If you look in the CF formula for a particular cell, it should tell you
which cell it is looking at to determine the format.

 

[rtbrd]

David,

The spread sheet is much more complex than I mentioned below, too much to
try and explain here. "Q7" is actually a range of 4 cells across, followed
by 4 blank rows, this being one group, and is 9 groups deep. There are 32 of
these groupings in total.

The CF does tell me the cell it is looking at, unfortunately it is going to
format the particular cell that contains the CF function, let's say cell
D100. When I sort the data the value in cell D100 moves to cell D99 and the
value in cell D99 moves to cell D100. The original value in D100 is the
value that sould be conditionally formatted but now resides in cell D99. The
new sorted value (not the one that should be formatted) is now in cell D100
and gets the formatting. Is there any way to cause the formatting to move
with the sort?

 

[rtbrd]

David,

I copied my decision matrix down and it now resides next to the data I want
to sort and this will work.

Thanx