Conditional formatting based on sorted data

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:20 PM GMT


QUESTION: I have a spread sheet that has multiple inventory items. Each of those items has multiple entries. All of this information is frequently sorted in various manners.

Each item must be tested twice per year. I figured out how to get conditional formatting to highlight the next test period cell based on 135 days since last test, 180 days since last test, and 225 days since last test.

The problem is that if I resort the data the formatting doesn't follow the last test date as it moves. I see that it shows the data for example as $A$12 which causes it to always look at that specific location. How do I get it to follow the pointed at information and not the specific cell number?

ANSWER: In your conditional formatting criteria remove the Dollar symbol which is present before the cell row reference 12. Make it as $A12 instead of $A$12. Your problem will be solved. This will freeze the cell $12.

---------- FOLLOW-UP ----------

QUESTION: I had tried to remove the $ from the 12. I find it still will not follow the targeted information all of the time. I can manually drag the rows out of order and place them around the page and the CF will continue to follow it.

However if I have the rows out of order and use the 'sort ascending' button, it will now point at the new information that moved into the specific cell again.

To hopefully give a better example of the spreadsheet - I have 5 rows with dates in column A. Then add a 6th row and place the CF in A6. Once that is done rearrange the rows and get the CF to follow along with the original target information no matter what row it is in.

ANSWER: In Conditional Formatting three things are most important. If you have failed in any of the below cases, then the CF will not run correctly.

1) Formula 2) Formula Construction 2) Activecell

Description:- Assume that you are having the data from ColumnA to ColumnF. First Row consist the Header (Naming) of your data and I want to highlight the columns D to Column H with Red Colour if BColumn value is less than CColumn Value

Formula:- So the formula should look like this. =B2<C2

Formula Construction:- =B2<C2 In the above formula the Columns alone needs to be freezed, otherwise the formula will keep on changing like C2<D2 for E column and D2<E2 for FColumn and so on¡­ So the formula should be like the below. =$B2<$C2 The final thing in Formula construction is the formula you are using in Conditional Formatting should return Boolean Value that is TRUE or FALSE statement. If you copy and paste the formula =$B2<$C2 in any of the cell then it will result TRUE or FALSE result.

Activecell:- It means that the placement of the cursor when applying the Conditional formatting. =$B2<$C2 Look the above formula it refers to B2&C2 cells. So the base row source of the Formula is Row2. So I have to place the cursor in any of the Column at the 2nd Row. In my above description I have mentioned that I would like the highlight Column D to Column H. So I have to place the cursor in D2 cell and extend the selection upto H2 in the right side and about the downwards as number of rows as I wish. For example I am extending the selection upto 500 rows from D2 cell. Here the placement of cursor according to that formula is called as ACTIVECELL (Active cell will have a white background after selection also). Here the D2 cell will have a white background.

Most of the people do mistakes in any of the three cases. The above is the very basic and most important when applying conditional formatting. I hope, it¡¯s clear now¡­

-- Sixthsense

---------- FOLLOW-UP ----------

QUESTION: I now think I see the problem.

With your example "Assume that you are having the data from ColumnA to ColumnF. First Row consist the Header (Naming) of your data and I want to highlight the columns D to Column H with Red Colour if BColumn value is less than CColumn Value"

I would have data from Column A1 to Column F1. However I want to highlight information in A25, and A256 etc. After a sort has been done though these might then be A1, A4, and A15.

If I make the color change based on one row the CF would then travel with the target information, but if I am making it with one row looking at another row which could change address it could be a problem.

I apologize for being slow on this, and greatly appreciate your help.


Now i can able to imagine your problem. For this you should not mention the cell address manually (like this $A$12 of $A12) in the conditional formatting, you should have to use formula to go with each data and check it with your criteria.

It will be easy for me if you send yourfile to my mail id for assisting you (for creating formula after seeing your data)...

My mail id:

-- Sixthsense


©2017 eLuminary LLC. All rights reserved.