need arrow based on cell color

[sksanjeev786]

Hi Team

I need a arrow based on A1 to G11 so what ever we have cell color same color I need in Arrow from A15 (example) including data...

A15 onward arrow should be in color up arrow green and red arrow down and data will remain black

book3
	A	B	C	D	E	F	G	H	I	J
1	53	37	38	16	11	13	11		RGB	RED
2	10	37	40	13	10	14	11		1460	255
3	10	38	38	15	7	13	10		208	0
4	63	41	44	19	10	11	9		80	0
5	56	39	42	16	12	15	11
6	63	31	40	17	15	12	12		Green	Red
7	60	47	43	22	12	19	17
8	65	49	55	27	24	16	15
9	52	41	41	17	15	13	15
10	72	40	53	17	12	14	16
11	58	30	45	13	15	14	13
12
13
14	Output need from A1 to G11
15	53 ▲							▼
16	55 ▼							▲
17	54 ▲
18
Sheet2

Cell Formulas
Range	Formula
H15	H15	=UNICHAR(9660)
H16	H16	=UNICHAR(9650)

 

[Sergius]

Hello! This should help. The arrows were first inserted into a separate cell using symbol insertion. Then copied and added to the list of formats. After that, UV rules were created, where the number format was selected from one created in advance.

Book1.xlsm
	A	B	C	D	E	F	G
1	53▲	37▲	38▲	16▼	11▼	13▼	11▼
2	10▼	37▲	40▲	13▼	10▼	14▼	11▼
3	10▼	38▲	38▲	15▼	7▼	13▼	10▼
4	63▲	41▲	44▲	19▼	10▼	11▼	9▼
5	56▲	39▲	42▲	16▼	12▼	15▼	11▼
6	63▲	31▲	40▲	17▼	15▼	12▼	12▼
7	60▲	47▲	43▲	22▲	12▼	19▼	17▼
8	65▲	49▲	55▲	27▲	24▲	16▼	15▼
9	52▲	41▲	41▲	17▼	15▼	13▼	15▼
10	72▲	40▲	53▲	17▼	12▼	14▼	16▼
11	58▲	30▲	45▲	13▼	15▼	14▼	13▼
Таблица12

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:G11	Cell Value	<20	text	NO
A1:G11	Cell Value	>20	text	NO

 

[sksanjeev786]

Hello! This should help. The arrows were first inserted into a separate cell using symbol insertion. Then copied and added to the list of formats. After that, UV rules were created, where the number format was selected from one created in advance.

Book1.xlsm
	A	B	C	D	E	F	G
1	53▲	37▲	38▲	16▼	11▼	13▼	11▼
2	10▼	37▲	40▲	13▼	10▼	14▼	11▼
3	10▼	38▲	38▲	15▼	7▼	13▼	10▼
4	63▲	41▲	44▲	19▼	10▼	11▼	9▼
5	56▲	39▲	42▲	16▼	12▼	15▼	11▼
6	63▲	31▲	40▲	17▼	15▼	12▼	12▼
7	60▲	47▲	43▲	22▲	12▼	19▼	17▼
8	65▲	49▲	55▲	27▲	24▲	16▼	15▼
9	52▲	41▲	41▲	17▼	15▼	13▼	15▼
10	72▲	40▲	53▲	17▼	12▼	14▼	16▼
11	58▲	30▲	45▲	13▼	15▼	14▼	13▼
Таблица12

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:G11	Cell Value	<20	text	NO
A1:G11	Cell Value	>20	text	NO

Hi Sergius,

Thanks for chekcing on this

I need the arrow only for the colored cell not for all the cells


I thnk for this we may need Macro

Regards
Sanjeev

 

[6StringJazzer]

Are the fill colors applied manually, or by conditional formatting?

If they are applied manually, and color is only thing that determines the direction of the arrow, then you will need VBA for this.

If they are applied by conditional formatting, what are the rules?

 

[sksanjeev786]

Are the fill colors applied manually, or by conditional formatting?

If they are applied manually, and color is only thing that determines the direction of the arrow, then you will need VBA for this.

If they are applied by conditional formatting, what are the rules?

Hi,

Thanks for checking on this.

We have copied the table from the PPT to the Excel file so there is not conditional formating on the file.

Regards
Sanjeev

 

[sksanjeev786]

Hi,

Thanks for checking on this.

We have copied the table from the PPT to the Excel file so there is not conditional formating on the file.

Regards
Sanjeev

Hi team

Any support on above things

 

[Sergius]

Is this what you wanted?

Book1
	A	B	C	D	E	F	G	H	I	J
1	53▲	37▲	38▲	16	11	13	11		RGB	RED
2	10▼	37▲	40▲	13	10	14	11		1460	255
3	10▼	38▲	38▲	15▲	7	13	10		208	0
4	63▲	41▲	44▲	19	10▼	11	9		80	0
5	56▲	39▲	42▲	16	12	15	11
6	63	31	40	17▼	15	12	12		Green	Red
7	60▲	47▲	43▲	22	12	19	17		5296274	255
8	65▲	49▲	55▲	27▲	24	16	15
9	52▲	41▲	41▲	17▲	15	13▼	15		▲▼
10	72▲	40▲	53▲	17	12	14	16
11	58	30	45	13▼	15	14	13
12
13
14	Output need from A1 to G11
15	53 ▲							▼
16	55 ▼							▲
17	54 ▲
Sheet1

Cell Formulas
Range	Formula
I7	I7	=COLOR_FILLS(A1)
J7	J7	=COLOR_FILLS(A2)
H15	H15	=UNICHAR(9660)
H16	H16	=UNICHAR(9650)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:G11	Expression	=COLOR_FILLS(A1)=5296274	text	NO
A1:G11	Expression	=COLOR_FILLS(A1)=255	text	NO

To use the Color_Fills function, add this code to the standard module

Public Function COLOR_FILLS(CELL As Range) As Double
COLOR_FILLS = CELL.Interior.Color
End Function

 

[sksanjeev786]

Is this what you wanted?

Book1
	A	B	C	D	E	F	G	H	I	J
1	53▲	37▲	38▲	16	11	13	11		RGB	RED
2	10▼	37▲	40▲	13	10	14	11		1460	255
3	10▼	38▲	38▲	15▲	7	13	10		208	0
4	63▲	41▲	44▲	19	10▼	11	9		80	0
5	56▲	39▲	42▲	16	12	15	11
6	63	31	40	17▼	15	12	12		Green	Red
7	60▲	47▲	43▲	22	12	19	17		5296274	255
8	65▲	49▲	55▲	27▲	24	16	15
9	52▲	41▲	41▲	17▲	15	13▼	15		▲▼
10	72▲	40▲	53▲	17	12	14	16
11	58	30	45	13▼	15	14	13
12
13
14	Output need from A1 to G11
15	53 ▲							▼
16	55 ▼							▲
17	54 ▲
Sheet1

Cell Formulas
Range	Formula
I7	I7	=COLOR_FILLS(A1)
J7	J7	=COLOR_FILLS(A2)
H15	H15	=UNICHAR(9660)
H16	H16	=UNICHAR(9650)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:G11	Expression	=COLOR_FILLS(A1)=5296274	text	NO
A1:G11	Expression	=COLOR_FILLS(A1)=255	text	NO

To use the Color_Fills function, add this code to the standard module

Public Function COLOR_FILLS(CELL As Range) As Double
COLOR_FILLS = CELL.Interior.Color
End Function

Hi,

Thank you so much for your help on this

Yes! i need same way appeared on the below table

I am not getting an arrow on the data could you please help me with this

I have written the code to conditional formatting

ie. =COLOR_FILLS(A1)=5296274 but not getting the arrow on data..

 

[Sergius]

Read my post #2. I wrote there how to add an arrow symbol.

 

[sksanjeev786]

Read my post #2. I wrote there how to add an arrow symbol.

Hi Sergius,

Thank you so much for your time on this I have tried as per post 2 and after that applied the formula you have provided
I am getting as below data along with arrow

got the color on cell but for arrow I am getting all down arrow