Add color in symbol

[sksanjeev786]

Hi Team,

Need to add color in arrow only with Green and Red

01 FS Grouping_Fix Macro.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	▲
3	▼			A	B	C	X				A	B	C	X
4
5			SEEN AD (NET)	19 ▼	15	22 ▼	16			SEEN AD (NET)	19 X	15	22 X	16
6			Correctly Branded (net)	3.87 ▼	3.67	4.09 ▼	3.68			Correctly Branded (net)	3.87 X	3.67	4.09 X	3.68
7			The Ad Is Likeable	3.15	3.14	3.17	3.19			The Ad Is Likeable	3.15	3.14	3.17	3.19
8			The Ad Told Me Something New	2.83 ▲	2.84	2.83	2.92 A			The Ad Told Me Something New	2.83	2.84	2.83	2.92 A
9			Top 2 Box (net)	2.75 ▲	2.69 ▲	2.82 ▲	2.95 ABC			Top 2 Box (net)	2.75	2.69	2.82	2.95 ABC
10			Top 2 Box (net)	2.91	2.99	2.84				Top 2 Box (net)	2.91	2.99	2.84
11
12			The Ad Is Likeable	3.27 ▼	3.2	3.35	3.25			The Ad Is Likeable	3.27 X	3.20	3.35	3.25
13			The Ad Told Me Something New	3 ▲	2.97 ▲	3.04 ▲	3.22 ABC			The Ad Told Me Something New	3.00	2.97	3.04	3.22 ABC
14			It Made Me Want To Visit The Dealership Or Take A Test Drive	2.93 ▲	2.9 ▲	2.97 ▲	3.16 ABC			It Made Me Want To Visit The Dealership Or Take A Test Drive	2.93	2.90	2.97	3.16 ABC
15			The Ad Made Me Want To Find Out More About The Vehicle (search Online/read Articles)	3.32	3.26	3.37				The Ad Made Me Want To Find Out More About The Vehicle (search Online/read Articles)	3.32	3.26	3.37
16			The Ad Made Me Want To Find Out More About The Brand	3.14	3.15	3.13				The Ad Made Me Want To Find Out More About The Brand	3.14	3.15	3.13
17			This Is The Sort Of Ad I Would Talk To Others About	3.07 ▲	3.08 ▲	3.05 ▲	3.28 ABC			This Is The Sort Of Ad I Would Talk To Others About	3.07	3.08	3.05	3.28 ABC
grouping

Cell Formulas
Range	Formula
D5:G10,D12:G17	D5	=SUBSTITUTE(K5,"X","")&" "&IFERROR(IF(FIND(D$3,$N5),$A$2,""),"")&IFERROR(IF(FIND($G$3,K5),$A$3,""),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K5:M27	Expression	=FIND($N$3,K5)	text	NO
K5:M27	Expression	=FIND(K$3,$N5)	text	NO
H5:I26	Expression	=FIND("M",#REF!)	text	NO
H5:I26	Expression	=FIND("N",#REF!)	text	NO

Regards,
Sanjeev

 

[johnnyL]

Under what conditions are you wanting to set the color of the arrow to red or green? Post some code you have for the arrows.

 

[Rick Rothstein]

If by "arrows" you mean those up and down pointing triangles... no, you will not be able to color them unless you color the text that is in the cell with them. Your values are the result of formulas... ONLY text constants can have parts of their text colored (or formatted for that matter) differently from the rest of the text in the cell... and even then, that can only be done manually or by VBA code.

 

[sksanjeev786]

If by "arrows" you mean those up and down pointing triangles... no, you will not be able to color them unless you color the text that is in the cell with them. Your values are the result of formulas... ONLY text constants can have parts of their text colored (or formatted for that matter) differently from the rest of the text in the cell... and even then, that can only be done manually or by VBA code.

Yes, sir Looking for a Macro in which I need color for arrows only.

 

[sksanjeev786]

Under what conditions are you wanting to set the color of the arrow to red or green? Post some code you have for the arrows.

Hello Sir,

Looking for a macro for arrow only

Regards,
Sanjeev

 

[Rick Rothstein]

Do you need to keep the formulas in Columns D through G? The reason I ask is because the only way a macro could work is if it replaced all of your formulas with the text values they are displaying (your formulas would no longer exist). If you need to keep the action your formulas are doing, then it MIGHT be possible to remove all of them and use VBA event code to perform the same action the formulas do.

 

[sksanjeev786]

Do you need to keep the formulas in Columns D through G? The reason I ask is because the only way a macro could work is if it replaced all of your formulas with the text values they are displaying (your formulas would no longer exist). If you need to keep the action your formulas are doing, then it MIGHT be possible to remove all of them and use VBA event code to perform the same action the formulas do.

Hello Sir,

Thanks for chekcing

I need only Arrow color need to be changed where ever I have up (green) and Down (Red) between D to F column only and rest will be same with data only color of arrow will change

Regards,
Sanjeev

 

[sksanjeev786]

Do you need to keep the formulas in Columns D through G? The reason I ask is because the only way a macro could work is if it replaced all of your formulas with the text values they are displaying (your formulas would no longer exist). If you need to keep the action your formulas are doing, then it MIGHT be possible to remove all of them and use VBA event code to perform the same action the formulas do.

Yes, Sir we can do that we can copy data from C to G and will copy in other columns with Text as you have mentioned above and apply the macro.

 

[johnnyL]

See if this satisfies you:

Sub InsertColumnsAndTriangles()                                         ' Up triangle = ChrW(9650) ... Down triangle = ChrW(9660) <-- Decimal values of the Hex
'                                                                       ' Up triangle = Unicode Hex 25B2 ... Down triangle = Unicode Hex 25BC
    Dim LastRow As Long
    Dim Cel     As Range
    Dim WS      As Worksheet
'
    Set WS = Sheets("Sheet1")                                           ' <--- Change to actual sheet name desired
''
    WS.Range("G:G, F:F, E:E").Insert                                    ' Insert 3 new columns
    WS.Range("E:E, G:G, I:I").ColumnWidth = 2.43                        ' set the column width for the 3 new columns
'
    WS.UsedRange.EntireColumn.AutoFit                                   ' Resize columns to fit headers/data
''
    LastRow = WS.Range("D" & Rows.Count).End(xlUp).Row                  ' Find the row of the last line of data in Column D
'
    For Each Cel In WS.Range("D1:H" & LastRow)
        If Right(Cel.Value, 2) = " " & ChrW(9650) Then                  ' If cell ends with a space and an up triangle then ...
            Cel.Value = Left(Cel.Value, Len(Cel.Value) - 2)             '   Remove the end space and triangle from the cell
            WS.Cells(Cel.Row, Cel.Column + 1) = ChrW(9650)              '   Place an up triangle in the column to the right of it
            WS.Cells(Cel.Row, Cel.Column + 1).Font.Color = vbGreen      '   Color the triangle Green
'
        ElseIf Right(Cel.Value, 2) = " " & ChrW(9660) Then              ' If cell ends with a space and a down triangle then ...
            Cel.Value = Left(Cel.Value, Len(Cel.Value) - 2)             '   Remove the end space and triangle from the cell
            WS.Cells(Cel.Row, Cel.Column + 1) = ChrW(9660)              '   Place a down triangle in the column to the right of it
            WS.Cells(Cel.Row, Cel.Column + 1).Font.Color = vbRed        '   Color the triangle Red
        End If
    Next
End Sub

 

[sksanjeev786]

See if this satisfies you:

Sub InsertColumnsAndTriangles()                                         ' Up triangle = ChrW(9650) ... Down triangle = ChrW(9660) <-- Decimal values of the Hex
'                                                                       ' Up triangle = Unicode Hex 25B2 ... Down triangle = Unicode Hex 25BC
    Dim LastRow As Long
    Dim Cel     As Range
    Dim WS      As Worksheet
'
    Set WS = Sheets("Sheet1")                                           ' <--- Change to actual sheet name desired
''
    WS.Range("G:G, F:F, E:E").Insert                                    ' Insert 3 new columns
    WS.Range("E:E, G:G, I:I").ColumnWidth = 2.43                        ' set the column width for the 3 new columns
'
    WS.UsedRange.EntireColumn.AutoFit                                   ' Resize columns to fit headers/data
''
    LastRow = WS.Range("D" & Rows.Count).End(xlUp).Row                  ' Find the row of the last line of data in Column D
'
    For Each Cel In WS.Range("D1:H" & LastRow)
        If Right(Cel.Value, 2) = " " & ChrW(9650) Then                  ' If cell ends with a space and an up triangle then ...
            Cel.Value = Left(Cel.Value, Len(Cel.Value) - 2)             '   Remove the end space and triangle from the cell
            WS.Cells(Cel.Row, Cel.Column + 1) = ChrW(9650)              '   Place an up triangle in the column to the right of it
            WS.Cells(Cel.Row, Cel.Column + 1).Font.Color = vbGreen      '   Color the triangle Green
'
        ElseIf Right(Cel.Value, 2) = " " & ChrW(9660) Then              ' If cell ends with a space and a down triangle then ...
            Cel.Value = Left(Cel.Value, Len(Cel.Value) - 2)             '   Remove the end space and triangle from the cell
            WS.Cells(Cel.Row, Cel.Column + 1) = ChrW(9660)              '   Place a down triangle in the column to the right of it
            WS.Cells(Cel.Row, Cel.Column + 1).Font.Color = vbRed        '   Color the triangle Red
        End If
    Next
End Sub

Thank you so much for your help in this
Just wanted to check can we get those arrows in the same cell given in the below example I have kept the image as attached with color arrow

Book1
	A	B	C	D	E	F	G
1
2	▲
3	▼			A	B	C	X
4
5			SEEN AD (NET)	19 ▼	15	22 ▼	16
6			Correctly Branded (net)	3.87 ▼	3.67	4.09 ▼	3.68
7			The Ad Is Likeable	3.15	3.14	3.17	3.19
8			The Ad Told Me Something New	2.83 ▲	2.84	2.83	2.92 A
9			Top 2 Box (net)	2.75 ▲	2.69 ▲	2.82 ▲	2.95 ABC
10			Top 2 Box (net)	2.91	2.99	2.84
11
12			The Ad Is Likeable	3.27 ▼	3.2	3.35	3.25
13			The Ad Told Me Something New	3 ▲	2.97 ▲	3.04 ▲	3.22 ABC
14			It Made Me Want To Visit The Dealership Or Take A Test Drive	2.93 ▲	2.9 ▲	2.97 ▲	3.16 ABC
15			The Ad Made Me Want To Find Out More About The Vehicle (search Online/read Articles)	3.32	3.26	3.37
16			The Ad Made Me Want To Find Out More About The Brand	3.14	3.15	3.13
17			This Is The Sort Of Ad I Would Talk To Others About	3.07 ▲	3.08 ▲	3.05 ▲	3.28 ABC
Sheet2