Custom data label color based on another cell Value (Priority)

[SereneSea]

Good morning,
Another question on my mind. Is there away to have data lables automatically change color based on another cell? I have a gantt chart and for high priority status (Column I) i would like the labels to change colors to be easier seen. I realize I can do this manually but this chart will be populated by many users so I want to ensure same formatting for high priority.

Thanks in advance!

 

[ebea]

You can do this, by using conditional formatting (just as you maybe have other places in the Gantt Chart). Mark the area you will use, and in conditional format choose, "New rule" and insert: IF(A$1=1;TRUE). A1 has to reflect the cell you have the value in, and 1 in the formula, are the value you will use.

 

[SereneSea]

hmm. forgive my misunderstanding. I already have conditional formatting in the I Column for priorities, (when "high" is selected it turns red, "medium" orange and "low" green), but how do i get the data labels ON the chart to reflect a different color based on what priority it is in the I column. As in, if "High" Priority is selected for the task, the color of the bars in the chart will turn red .

 

[ebea]

It's difficult, only out from your description, to "see" exactly how your formatting looks, and what you actual want. Is it correct understood, out of your latest description, that you want to have the label in Column I, to change out from what are the priority in that Column?
Your question was, "...change color, based on another cell"!

 

[SereneSea]

Sorry for my miscommunication. I am talking about conditional formatting on the gantt (bar chart). I have attached a photo . I would like the circled to be a different label according to my priority cell in the table.

 

[ebea]

Sorry for my miscommunication. I am talking about conditional formatting on the gantt (bar chart). I have attached a photo . I would like the circled to be a different label according to my priority cell in the table.



View attachment 57608

Without having some data, it would be difficult to give a clear answer. I do not know, what lays behind your formatting, so it will only be guessing.
A Gantt Chart formatting, are normally based on date range, which control the formatting (that's the idea with a Gantt Chart). Try use the XL2BB tool, to upload some data.

 

[SereneSea]

example with chart.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1						2022						DO not delete Columns K to O please
2
3						Plan Start	2/1/2022	12/31/2022		% Complete	0%
4	Concat	Department	Test	Trainer	Trainee	Trainee Area	PRIORITY	START	PLAN END	ACTUAL END	% COMPLETE	Project Start-(chart start)	Days to Start	Complete	Incomplete	Plan Days
5	Unicorn (T: whiskers)	Alpha	Unicorn	whiskers	Bird	Delta	MEDIUM	4/5/2022	7/2/2022		0%	2/1/2022	63	0	88	88
6	Frog (T: Pluto)	Omega	Frog	Pluto	Whiskers	Omega	HIGH	4/5/2022	6/3/2022		0%	2/1/2022	63	0	59	59
7	Cat (T: Saturn)	Delta	Cat	Saturn	Pluto	Alpha	LOW	4/5/2022	6/3/2022		0%	2/1/2022	63	0	59	59
8
Entry

Cell Formulas
Range	Formula
M3	M3	=AVERAGE(Table1[% COMPLETE])
N5:N7	N5	=$I$3
O5:O7	O5	=IF(ISBLANK(J5),0,J5-N5)
P5:P7	P5	=$M5*$R5
Q5:Q7	Q5	=R5-P5
R5:R7	R5	=IF(ISBLANK(K5),0,K5-J5)
C5:C7	C5	=CONCAT([@Test]," (T: ",[@Trainer],")")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I5	Cell Value	contains "LOW"	text	NO
I5	Cell Value	contains "MEDIUM"	text	NO
I5	Cell Value	contains "HIGH"	text	NO
M3	Other Type	Color scale		NO
M5:M8	Other Type	DataBar		NO
I4,I6:I7	Cell Value	contains "LOW"	text	NO
I4,I6:I7	Cell Value	contains "MEDIUM"	text	NO
I4,I6:I7	Cell Value	contains "HIGH"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
I5:I7	List	HIGH,MEDIUM,LOW
M5:M7	List	=$F$20:$F$24

 

[ebea]

I'm not sure, if it was similar to this, you had in mind.

Edited: See below

 

[ebea]

Some formatting was missing

Mappe1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1						2022						DO not delete Columns K to O please
2
3						Plan Start	02-01-2022	12/31/2022		% Complete	=AVERAGE(Table1[% COMPLETE])
4	Concat	Department	Test	Trainer	Trainee	Trainee Area	PRIORITY	START	PLAN END	ACTUAL END	% COMPLETE	Project Start-(chart start)	Days to Start	Complete	Incomplete	Plan Days
5	=CONCAT([@Test]," (T: ",[@Trainer],")")	Alpha	Unicorn	whiskers	Bird	Delta	HIGH	04-05-2022	07-02-2022		0	02-01-2022	122	0	-86	-86
6	=CONCAT([@Test]," (T: ",[@Trainer],")")	Omega	Frog	Pluto	Whiskers	Omega	MEDIUM	04-05-2022	06-03-2022		0	02-01-2022	122	0	-59	-59
7	=CONCAT([@Test]," (T: ",[@Trainer],")")	Delta	Cat	Saturn	Pluto	Alpha	LOW	04-05-2022	06-03-2022		0	02-01-2022	122	0	-59	-59
Ark1

Cell Formulas
Range	Formula
N5:N7	N5	=$I$3
O5:O7	O5	=IF(ISBLANK(J5),0,J5-N5)
P5:P7	P5	=$M5*$R5
Q5:Q7	Q5	=R5-P5
R5:R7	R5	=IF(ISBLANK(K5),0,K5-J5)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I4	Expression	=I5=$T$2	text	NO
I4	Expression	=$I$5:$I$7=$T$1	text	NO

 

[SereneSea]

hmm its not colors of my bars in the chart.