Specific Cell with Conditional formatting in a table - Sorting Problem.

[pikalex]

Hi Mr. Excel community.

I am facing a problem with a table with contains only few specific cells with a conditional formating based on its own value. The problem is that when I sort the table in any way, the conditional formatting in the specific cell does not follow the row it's in. It stays at the same absolute cell when the conditional formatting was first created.

I created an example of the Excel file at this link with more information in it : SortingTableConditionalProblem.xlsx.

SortingTableConditionalProblem.xlsx
	B	C	D
4	Expense	Yearly	Monthly
5	B (User input on Monthly)	-12	-1
6	A (User input Yearly)		0
7	C (User input on Yearly)	0	0
8	D (User input on Monthly)	12	1
Sheet1

Cell Formulas
Range	Formula
C5,C8	C5	=12*[@Monthly]
D6:D7	D6	=[@Yearly]/12

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C6	Cell	contains a blank value	text	NO
C6	Cell Value	>=0	text	NO
C6	Cell Value	<0	text	NO
D5	Cell	contains a blank value	text	NO
D5	Cell Value	>=0	text	NO
D5	Cell Value	<0	text	NO
D8	Cell	contains a blank value	text	NO
D8	Cell Value	>=0	text	NO
D8	Cell Value	<0	text	NO
C7	Cell	contains a blank value	text	NO
C7	Cell Value	>=0	text	NO
C7	Cell Value	<0	text	NO

Row A and C have a formula in Monthly column based on the yearly cell calue entered by the user.
Row B and D have a formula in Yearly column based on the Monthly cell calue entered by the user.

Each cell to be entered by the user has to be Highlited with the following rules:
1) Dark Green if the field is empty.
2) Light Green if the field is greater or equal to 0.
3) Red if the field is lower than 0.

After sorting A -> Z, the output is bad. The cell conditional formatting stayed at the same place so color code does match the cell that the user has to input and the color code applies to the cell with a formula in it...

SortingTableConditionalProblem.xlsx
	B	C	D
15	Expense	Yearly	Monthly
16	A (User input Yearly)		0
17	B (User input on Monthly)	-12	-1
18	C (User input on Yearly)	0	0
19	D (User input on Monthly)	12	1
Sheet1

Cell Formulas
Range	Formula
D16,D18	D16	=[@Yearly]/12
C17,C19	C17	=12*[@Monthly]

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C17	Cell	contains a blank value	text	NO
C17	Cell Value	>=0	text	NO
C17	Cell Value	<0	text	NO
D16	Cell	contains a blank value	text	NO
D16	Cell Value	>=0	text	NO
D16	Cell Value	<0	text	NO
D19	Cell	contains a blank value	text	NO
D19	Cell Value	>=0	text	NO
D19	Cell Value	<0	text	NO
C18	Cell	contains a blank value	text	NO
C18	Cell Value	>=0	text	NO
C18	Cell Value	<0	text	NO

When we look at the Conditional Formatting Rules Manager, we can that the rules "Applies to" an absolute cell. If I try to remove the $ signs to make it relative, when I click on Apply, the $ signs automatically reappear…

I need new ideas to solve this problem. Anyone?

Thank you for your time.

 

[Toadstool]

Hi Pikalex,

Try removing the duplicate formats and have the "Applies to" change to the range of all cells you want to conditionally format. As it's a table it should then automatically extend the conditional format to any added rows.

 

[pikalex]

Hi Toadstool,

It would work fine if I wanted to apply the rules for every cells of these 2 columns. The thing is I just want to color the cells where the user has to enter a value, not the one with formulas.

 

[pikalex]

I used Toadstool's idea with more specific formulas to get what I needed. The idea is to look if the cell contains a formula and looking if the cell is either blank, negative or <=0 in this specific order.

SortingTableConditionalProblem.xlsx
	B	C	D
35	Expense	Yearly	Monthly
36	B (User input on Monthly)	-12	-1
37	A (User input Yearly)		0
38	C (User input on Yearly)	0	0
39	D (User input on Monthly)	12	1
Sheet1

Cell Formulas
Range	Formula
C36,C39	C36	=12*[@Monthly]
D37:D38	D37	=[@Yearly]/12

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C36:D39	Expression	=AND(ISFORMULA(C36)=FALSE,C36<0)	text	NO
C36:D39	Expression	=AND(ISFORMULA(C36)=FALSE,ISBLANK(C36))	text	NO
C36:D39	Expression	=AND(ISFORMULA(C36)=FALSE,C36>=0)	text	NO

Now the problem is that is the user enters a formula the user cell will dissappear. This may be alright in certain cases or not.

Altough it is not perfect it is the closest I can get and it will be good enough for now. I think it can be improved to resolve this.