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)

 

[MARK858]

@sksanjeev786 why not use the built in conditional formatting icons (assuming that the green 17 in post one is incorrect and that the exact shade of red and green isn't that important)?

Book1
	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
13
14	Output need from A1 to G11
15	53	37	38	16	11	13	11
16	10	37	40	13	10	14	11
17	10	38	38	15	7	13	10
18	63	41	44	19	10	11	9
19	56	39	42	16	12	15	11
20	63	31	40	17	15	12	12
21	60	47	43	22	12	19	17
22	65	49	55	27	24	16	15
23	52	41	41	17	15	13	15
24	72	40	53	17	12	14	16
25	58	30	45	13	15	14	13
Sheet5

Cell Formulas
Range	Formula
A15:G25	A15	=A1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A15:G25	Other Type	Icon set		NO

 

[Sergius]

Hello! Remove the last two rules where the number 20 is compared. Then edit the remaining rules by indicating the format of the number with the arrow. You should have this

 

[MARK858]

@sksanjeev786 What result do you get with the code below?

Please note that I have used the color code for green based on your xl2bb in post one, as the RGB numbers you posted for green do not come out as green for me (this might need adjusting as I can't 100% trust that the XL2BB is exact).

Obviously I still think you need to look at the green up arrow in D9

Sub ColorArrow2()
    Dim myCell As Range
    Application.ScreenUpdating = False
    
    For Each myCell In Range("A1:G11")
        
        If myCell.Interior.ColorIndex = xlNone Then
            myCell.Offset(14).Value = myCell.Value
        Else
            If myCell.Interior.Color = 5296274 Then  '  The RGB you quoted doesn't come out as green to me
                myCell.Offset(14) = myCell.Value & "    " & ChrW(9650)
                myCell.Offset(14).Characters(Len(myCell.Offset(14))).Font.Color = 5296274
            Else
                myCell.Offset(14) = myCell.Value & "    " & ChrW(9660)
                myCell.Offset(14).Characters(Len(myCell.Offset(14))).Font.Color = 255
            End If
        End If

    Next
    
    Range("A15:G25").HorizontalAlignment = xlLeft

End Sub

The results I get with the code are...

 

[sksanjeev786]

Hello! Remove the last two rules where the number 20 is compared. Then edit the remaining rules by indicating the format of the number with the arrow. You should have this

Thank you so much for looking into it.

Sure! I will check and let you know if I have any questions

 

[sksanjeev786]

@sksanjeev786 What result do you get with the code below?

Please note that I have used the color code for green based on your xl2bb in post one, as the RGB numbers you posted for green do not come out as green for me (this might need adjusting as I can't 100% trust that the XL2BB is exact).

Obviously I still think you need to look at the green up arrow in D9

Sub ColorArrow2()
    Dim myCell As Range
    Application.ScreenUpdating = False
   
    For Each myCell In Range("A1:G11")
       
        If myCell.Interior.ColorIndex = xlNone Then
            myCell.Offset(14).Value = myCell.Value
        Else
            If myCell.Interior.Color = 5296274 Then  '  The RGB you quoted doesn't come out as green to me
                myCell.Offset(14) = myCell.Value & "    " & ChrW(9650)
                myCell.Offset(14).Characters(Len(myCell.Offset(14))).Font.Color = 5296274
            Else
                myCell.Offset(14) = myCell.Value & "    " & ChrW(9660)
                myCell.Offset(14).Characters(Len(myCell.Offset(14))).Font.Color = 255
            End If
        End If

    Next
   
    Range("A15:G25").HorizontalAlignment = xlLeft

End Sub

The results I get with the code are...

View attachment 119474

Thank you so much for providing the macro I will check and let u know if I have any questions

Really appreciate your hard work on this

 

[sksanjeev786]

Hello! Remove the last two rules where the number 20 is compared. Then edit the remaining rules by indicating the format of the number with the arrow. You should have this

could you please share the formula i can not able to open the image

 

[sksanjeev786]

Thank you so much for providing the macro I will check and let u know if I have any questions

Really appreciate your hard work on this

Hi Sir,

I have tried with the macro and getting below result

arrow has not applied in all the color cell and I need only 1 space after data.. currently I am getting more space after data...

book1
	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	60	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
15	53 ▲	37 ▲	38 ▲	16	11	13	11
16	10 ▼	37 ▲	40 ▲	13	10	14	11
17	10 ▼	38 ▲	38 ▲	15 ▲	7	13	10
18	63 ▲	41 ▲	44 ▲	19	10 ▼	11	9
19	56 ▲	39 ▲	42 ▲	16	12	15	11
20	63	31	40	17 ▼	15	12	12
21	60 ▲	47 ▲	43 ▲	22	12	19	17
22	60 ▲	49 ▲	55 ▲	27 ▲	24	16	15
23	52 ▲	41 ▲	41 ▲	17 ▲	15	13 ▼	15
24	72 ▲	40 ▲	53 ▲	17	12	14	16
25	58	30	45	13 ▼	15	14	13
Sheet1 (2)

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

 

[Sergius]

Book1.xlsm
	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 ▲
Таблица12

Cell Formulas
Range	Formula
I7	I7	=COLOR_FILLS(A1)
J7	J7	=COLOR_FILLS(A3)

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

 

[sksanjeev786]

Book1.xlsm
	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 ▲
Таблица12

Cell Formulas
Range	Formula
I7	I7	=COLOR_FILLS(A1)
J7	J7	=COLOR_FILLS(A3)

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

Hi,

I don't know if am missing any things but not getting any arrow after applying the conditional formatting

 

[Sergius]

To do this, you must use the "symbol" menu to insert the arrows into any cell. T.K. These are ordinary characters, they need to be copy, go into the cell format and choose the last item "Custom". We write 0 into the line "type" and insert the arrow up next to it. We do the same with another arrow. As a result, you will have two entries with arrows in your user format list. Next, edit conditional formatting rules. To do this, for each of them, install the cell format, I choose the early added "0" with the arrow. Until you indicate the range of cells for which these rules will work. You have in the first rule, formatting will only be applied to cell "A".