Move conditional formatting with rows when sorting data?

[SimonJensenAus]

Hello,
I have been applying some conditional formatting to some data in excel, ie: rows 4 and 9.
But when I then sort the data via highest to lowest value, the conditional formatting stays in row 4 and 9, and does not follow the data to the new row after being sorted.
Is there a way to lock in conditional formatting to follow the data after sorting?

 

[Peter_SSs]

Welcome to the MrExcel board!

Is there a way to lock in conditional formatting to follow the data after sorting?

There is no in-built way that I know of. A work-around can often be employed though.

Some more information first please:
Are you saying that only rows 4 and 9 have Conditional Formatting applied and the other rows do not have CF applied?
.. or are you saying that all rows have the CF but only rows 4 and 9 meet the condition(s) and are formatted differently as a result?

What are your CF rules?

The best way to answer those questions would be to post a small section of your sheet (any sensitive data altered) with XL2BB

 

[SimonJensenAus]

Welcome to the MrExcel board!


There is no in-built way that I know of. A work-around can often be employed though.

Some more information first please:
Are you saying that only rows 4 and 9 have Conditional Formatting applied and the other rows do not have CF applied?
.. or are you saying that all rows have the CF but only rows 4 and 9 meet the condition(s) and are formatted differently as a result?

What are your CF rules?

The best way to answer those questions would be to post a small section of your sheet (any sensitive data altered) with XL2BB

thank you for the quick reply,
What I am trying to achieve is to set up a conditional format that colors the cells in a row dependent of the value in column A.
What I was testing to do is conditionally color 1 row and see if it would follow the row after sorting.
I have about 3500 rows sorted by name, with different conditions of 1,2,5,10 (GB allowance) . I was trying to color code the values dependent of condition (GB Used)
But all of the names have different GB allowance, so the values can be 1,2,1,10,5,2,10,2, etc,
I was trying to sort all of the GB allowance together (all the 1 together, all the 2 together) and then apply a conditional format to the rows.
And then resort the rows back into the names, I was trying to get the conditional format to follow the rows. and not be absolute, But I don't think it is possible.
Excel gives me the error in the picture. I have tried to set the Maximum value as $A:$A - but it doesn't work either.

 

[Peter_SSs]

If you are trying to use a color scales then yes, I think that you are in trouble.
Conditional Formats using a formula can sometimes be arranged with a work-around to follow the sorting but I don't know of a way with color scales.