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]

That should be doable if you absolutely need that, But I need some sleep right now. I should be back on in about 12 hrs.

 

[sksanjeev786]

That should be doable if you absolutely need that, But I need some sleep right now. I should be back on in about 12 hrs.

Sure Johnny please take you time

Have a nice Evening ahead

 

[johnnyL]

That should be doable if you absolutely need that

Well, it is not doable because of the formulas.

 

[sksanjeev786]

Well, it is not doable because of the formulas.

Hi Johnny,
I have removed the formula and only add the data with Arrow only I hope now we can do it

book1
	C	D	E	F	G
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
Sheet1

 

[johnnyL]

You can leave all formulas in your sheet.

The following code will remove any formula that gets in the way of changing the color of the triangle in the columns D - F range:

Sub ColorTrianglesInCells()                                             ' 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
'
    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:F" & LastRow)                              ' Range loop
        If Right(Cel.Value, 1) = ChrW(9650) Then                            '   If cell ends with an up triangle then ...
            Cel.Value = Cel.Value                                           '       Remove the formula from the cell to allow a partial color change
            Cel.Characters(Start:=Len(Cel), Length:=1).Font.Color = vbGreen '       Color the triangle Green
        ElseIf Right(Cel.Value, 1) = ChrW(9660) Then                        '   If cell ends with a down triangle then ...
            Cel.Value = Cel.Value                                           '       Remove the formula from the cell to allow a partial color change
            Cel.Characters(Start:=Len(Cel), Length:=1).Font.Color = vbRed   '       Color the triangle Red
        End If
    Next                                                                    ' Loop back
End Sub

Be sure to set the proper sheet name at the top of the code. ;)

 

[Rick Rothstein]

Give this macro a try...

Sub ColorTriangles()
  Dim Cell As Range
  For Each Cell In Range("D5", Cells(Rows.Count, "G").End(xlUp))
    If Len(Cell.Value) Then
      If AscW(Right(Cell.Value, 1)) = 9650 Then
        Cell.Characters(Len(Cell.Value) - 1).Font.Color = 5287936
      ElseIf AscW(Right(Cell.Value, 1)) = 9660 Then
        Cell.Characters(Len(Cell.Value) - 1).Font.Color = vbRed
      End If
    End If
  Next
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (ColorTriangles) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[sksanjeev786]

Give this macro a try...

Sub ColorTriangles()
  Dim Cell As Range
  For Each Cell In Range("D5", Cells(Rows.Count, "G").End(xlUp))
    If Len(Cell.Value) Then
      If AscW(Right(Cell.Value, 1)) = 9650 Then
        Cell.Characters(Len(Cell.Value) - 1).Font.Color = 5287936
      ElseIf AscW(Right(Cell.Value, 1)) = 9660 Then
        Cell.Characters(Len(Cell.Value) - 1).Font.Color = vbRed
      End If
    End If
  Next
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (ColorTriangles) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Thank you So much Sir......

I work very well as per the requirement.

Regards,
Sanjeev

 

[sksanjeev786]

You can leave all formulas in your sheet.

The following code will remove any formula that gets in the way of changing the color of the triangle in the columns D - F range:

Sub ColorTrianglesInCells()                                             ' 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
'
    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:F" & LastRow)                              ' Range loop
        If Right(Cel.Value, 1) = ChrW(9650) Then                            '   If cell ends with an up triangle then ...
            Cel.Value = Cel.Value                                           '       Remove the formula from the cell to allow a partial color change
            Cel.Characters(Start:=Len(Cel), Length:=1).Font.Color = vbGreen '       Color the triangle Green
        ElseIf Right(Cel.Value, 1) = ChrW(9660) Then                        '   If cell ends with a down triangle then ...
            Cel.Value = Cel.Value                                           '       Remove the formula from the cell to allow a partial color change
            Cel.Characters(Start:=Len(Cel), Length:=1).Font.Color = vbRed   '       Color the triangle Red
        End If
    Next                                                                    ' Loop back
End Sub

Be sure to set the proper sheet name at the top of the code. ;)

Awesome!!!!! Always happy with this team and get assistance in our hard times

 

[johnnyL]

Glad we could help @sksanjeev786.

 

[sksanjeev786]

Glad we could help @sksanjeev786.

Hi Team,

Hope you are doing well

Just wanted to check I have changed the Arrow font from Ariel to Wingding 3 and now getting C and E data instead of Arrow.
I know it bit tricky but is it possible? to have arrow I have kept in A2 and A3 or Any idea if you can give to me

For arrow ref. i have attached the Screen shot

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
Sheet1