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)

 

[sksanjeev786]

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".

Hi,

I have added arrows for 1 and 2 column and after that added formula in Conditional formatting but still not getting
May be I am missing anything...

I know you have spent lot of time on this.... it oky mam you can ignore this......but just wanted to share this ...

thank you so much for your help

 

[MARK858]

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...

How are the cells that don't have the arrows colored as I get the below with that XL2BB with normal formatting (as you previously stated), you won't get arrows if you have changed it to conditional formatting.

 

[Sergius]

Carefully look at the photo from my post #12. In the list of rules, pay attention to the column "format". I have a number with an arrow there, you do not have this. Select the rule and click "Edit the rule". In the next window below, press the "Format" button. After that, select the first tab, and in it the last item "Custom". In the list, select the format of the number with the arrow you created.

 

[MARK858]

How are the cells that don't have the arrows colored as I get the below with that XL2BB with normal formatting (as you previously stated), you won't get arrows if you have changed it to conditional formatting.

If you have now used conditional formatting (or normal formatting or a mix of the 2) then use the code

Sub ColorArrow3()
    Dim myCell As Range
    Application.ScreenUpdating = False
   
    For Each myCell In Range("A1:G11")
       
        If myCell.DisplayFormat.Interior.ColorIndex = xlNone Then
            myCell.Offset(14).Value = myCell.Value
        Else
            If myCell.DisplayFormat.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

 

[sksanjeev786]

Carefully look at the photo from my post #12. In the list of rules, pay attention to the column "format". I have a number with an arrow there, you do not have this. Select the rule and click "Edit the rule". In the next window below, press the "Format" button. After that, select the first tab, and in it the last item "Custom". In the list, select the format of the number with the arrow you created.

Got it...!!

Thank you soooo much for your support on this..

really appreciate your effort on this

 

[sksanjeev786]

If you have now used conditional formatting (or normal formatting or a mix of the 2) then use the code

Sub ColorArrow3()
    Dim myCell As Range
    Application.ScreenUpdating = False
  
    For Each myCell In Range("A1:G11")
      
        If myCell.DisplayFormat.Interior.ColorIndex = xlNone Then
            myCell.Offset(14).Value = myCell.Value
        Else
            If myCell.DisplayFormat.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

Perfect... !!!

thank you so much sir for your help on this

 

[MARK858]

Perfect... !!!

thank you so much sir for your help on this

You're welcome, happy we could help