Conditional Formatting Colors after sorting

T

toongal12

Board Regular
Joined
Dec 1, 2016
Messages
150
I've read a lot and I can't seem to find a solution that works for me.

My conditional formatting reads from another table. It colors correctly. But if I try to Custom Sort by color, it looses the color.

My table reads for "Animal","Date","Gender". The colors is set to color red if a there is a "Cat" and "Female". I have the conditional format to read the other table with =AND(cell="cat",other_cell="female"), which works.

So when I try to custom sort for a color, it moved the row to the top, but the color stays in the row it was previously in.

I tried adding a hidden column with "Cat" in one cell and "Female" below it. The cells moved to the top again, but lost all color overall.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Hello, I may need to see a sample of your layout, but unless I have misunderstood what you wrote, it sounds like you set your Conditional Formatting using another Table that you are not sorting, so the colors would stay where they are. If that is the case, you should conditionally format the table you are sorting.

Please proved a sample of your layout or explain further.
 
Upvote 0
Joyner said:
Hello, I may need to see a sample of your layout, but unless I have misunderstood what you wrote, it sounds like you set your Conditional Formatting using another Table that you are not sorting, so the colors would stay where they are. If that is the case, you should conditionally format the table you are sorting.

Please proved a sample of your layout or explain further.
Click to expand...

Conditioning format is set on the same table. Selected cells are shown to be the same table. The values move, but the colors don't move.

I have an INDEX-MATCH to provide a list and a rough sketch of a table. To be neater, on a separate sheet, I called the cells over to make a table. The "=Sheet!L##" style reference call. I condition formated the called cells.
 
Upvote 0
Sorry, still not following, if you have the conditional formatting on the table being sorting (and by that I also mean the CF formulas used refer to that same table), why do you mention in the first post: "...I have the conditional format to read the other table" and you again mention above "..I called the cells over to make a table." I don't understand this part: "I condition formatted the called cells. "

Are the CF formulas looking at the table with the conditional formats? In other words, the "other" table in not used at all of purposes of this discussion.
 
Upvote 0
Just as an afterthought and to be sure, is calculation turned to automatic?
 
Upvote 0
Joyner said:
Sorry, still not following, if you have the conditional formatting on the table being sorting (and by that I also mean the CF formulas used refer to that same table), why do you mention in the first post: "...I have the conditional format to read the other table" and you again mention above "..I called the cells over to make a table." I don't understand this part: "I condition formatted the called cells. "

Are the CF formulas looking at the table with the conditional formats? In other words, the "other" table in not used at all of purposes of this discussion.
Click to expand...
I cannot edit the first post after a certain amount of time so I cannot change the wording of the post. Please ignore it, as it is worded incorrectly.


I do not know the correct terminology so I call it "calling".

Example: Sheet 1 has cell A1 with a value. Sheet 2, I want to bring it over. so I would put "=", then click on the value and you will see "=Sheet1!A1". I do not know how else to explain that part. Referencing, calling, I do not know the phrase.

So the Sheet2 with these values from Sheet1. On Sheet2, I select those cells and they are condition formatted. I selected Sheet2, and nothing of Sheet1 has to do with the condition formatting. It is formatted on sheet 2 for the sheet2 table.
 
Upvote 0
Okay, none for that should affect your conditional formatting or the way it sorts. Can you give me the actual formula you are using.

=AND(cell="cat",other_cell="female")

So change cell and other cell to the actual references
 
Upvote 0
Joyner said:
Okay, none for that should affect your conditional formatting or the way it sorts. Can you give me the actual formula you are using.

=AND(cell="cat",other_cell="female")

So change cell and other cell to the actual references
Click to expand...
=AND(Sheet1!$D17="Cat",Sheet1!$E17="Female")
 
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Solved
Sorting with conditional formatting rules
Replies
2
Views
140
Yamasaki450
Y
J
  • Solved
Program Doesn't Allow Conditional Change of Font Color Without Affecting Fill
Replies
1
Views
223
jdpro
J
V
  • Solved
Conditional Formatting Formula
Replies
2
Views
490
awoohaw
awoohaw
H
  • Solved
Using Macro/VLOOKUP to return format of cell
Replies
9
Views
92
mumps
mumps
L
  • Solved
Evaluate each cell in a row against two other rows using VBA
Replies
5
Views
288
mumps
mumps

Forum statistics

Threads
1,214,651
Messages
6,120,739
Members
448,989
Latest member
mariah3

Share this page

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