Need to change font color

[sksanjeev786]

Hi Team,

I would like to change the Grey color data to Black with out changing the Arrow symbol (p,q r ,are in Wingding font with Red and Green color) can anyone help me on this

Book1
	A	B	C	D	E	F	G	H
1	Unaided Brand Awareness (First Mention)	27	-0.9	-6.0 q	27	0.4	27	1.5
2	Unaided Brand Awareness (Any Mention)	47	0.8	-6.5 q	47	-0.2	47	-0.2
3	Aided Brand Awareness	85	3.6 r	3.9 r	85	1	85	1.5
4	Message Association	17	-2.8	-3.9	17	0.7	17	0.5
5	Book Intent - Business	29	0.2	1.2	29	-0.9	29	-0.6
6	Book Intent - Personal/Leisure	59	0	0.3	59	0.9	59	0.1
7	Leisure Travel Consideration	60	3.2	3.3	60	2	60	-0.2
8	Business Travel Consideration	9	-2.5	-1	9	-2.5	9	-4.3 q
9	Affinity	53	3.6	-1.5	53	-3.8	53	-1.4
10	Unique	32	3.9	-0.5	32	-3.9	32	6.0 p
11	Meets Needs	52	-1.8	0.1	52	-0.5	52	-0.7
Sheet1

 

[Micron]

So r's should look like one of these? Space or no space?

Oddly enough the minus values cause Excel to complain about the formula in the cell but there doesn't seem to be one.

 

[Micron]

You could try this on a copy of your sheet

Sub ColourNumbers()
Dim Lrow As Long, Lcol As Long
Dim rng As Range

With Sheets("007") 'change to your sheet name or use With ActiveSheet
    Lrow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Lcol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End With
For Each rng In Range(Cells(1, 2), Cells(Lrow, Lcol))
    If Trim(InStr(rng, " ")) = 0 Then
        rng.Font.Color = vbBlack
    Else
        rng.Characters(1, InStr(rng, " ")).Font.Color = vbBlack
    End If
Next

End Sub

 

[sksanjeev786]

Hi Team,

I would like to change the Grey color data to Black with out changing the Arrow symbol (p,q r ,are in Wingding font with Red and Green color) can anyone help me on this

Book1
	A	B	C	D	E	F	G	H
1	Unaided Brand Awareness (First Mention)	27	-0.9	-6.0 q	27	0.4	27	1.5
2	Unaided Brand Awareness (Any Mention)	47	0.8	-6.5 q	47	-0.2	47	-0.2
3	Aided Brand Awareness	85	3.6 r	3.9 r	85	1	85	1.5
4	Message Association	17	-2.8	-3.9	17	0.7	17	0.5
5	Book Intent - Business	29	0.2	1.2	29	-0.9	29	-0.6
6	Book Intent - Personal/Leisure	59	0	0.3	59	0.9	59	0.1
7	Leisure Travel Consideration	60	3.2	3.3	60	2	60	-0.2
8	Business Travel Consideration	9	-2.5	-1	9	-2.5	9	-4.3 q
9	Affinity	53	3.6	-1.5	53	-3.8	53	-1.4
10	Unique	32	3.9	-0.5	32	-3.9	32	6.0 p
11	Meets Needs	52	-1.8	0.1	52	-0.5	52	-0.7
Sheet1

Hi Micron,

Thank you so much for your help and i have applied your macro and look like data color getting black but on the statement, only the 1st-word color is applied and after that statement color is in Grey.

Regards
sanjeev

 

[sksanjeev786]

You could try this on a copy of your sheet

Sub ColourNumbers()
Dim Lrow As Long, Lcol As Long
Dim rng As Range

With Sheets("007") 'change to your sheet name or use With ActiveSheet
    Lrow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Lcol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End With
For Each rng In Range(Cells(1, 2), Cells(Lrow, Lcol))
    If Trim(InStr(rng, " ")) = 0 Then
        rng.Font.Color = vbBlack
    Else
        rng.Characters(1, InStr(rng, " ")).Font.Color = vbBlack
    End If
Next

End Sub

Hi Sir,

Can we add (.0) if we have a number with 1 or 2 as 1.0 and 2.0 in after generating macro where ever we have single digit i need to add (.0)

could you please help me on this

 

[Micron]

(p,q r ,are in Wingding font with Red and Green color)

I didn't code for that because you said those symbols were already coloured. I will have to adapt for 3 situations but you will have to advise which character gets which colour.

 

[sksanjeev786]

I didn't code for that because you said those symbols were already coloured. I will have to adapt for 3 situations but you will have to advise which character gets which colour.

Sure Sir,

below is the charter has 3 different colors and in the above macro all looks good only i need .0 data if we have 1 or 2 as 1.0 and 2.0

p= Green
r= Hollow Green
q= red

 

[Micron]

I have no idea what hollow green is and didn't find out by searching. If you don't like what I chose for a lighter green, then adjust the 153,255,153 values. In fact, you could tweak all of the colours if you use the RGB method rather than vbRed or vbGreen if you like, but I leave that to you.
This seems to work

Sub ColourNumbers()
Dim Lrow As Long, Lcol As Long, i As Integer
Dim rng As Range

With ActiveSheet
     Lrow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
     Lcol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End With

For Each rng In Range(Cells(1, 2), Cells(Lrow, Lcol))
     If Not rng Is Nothing Then
         rng.Characters(1, InStr(rng, " ")).Font.Color = vbBlack
         Select Case Right(rng, 1)
             Case "p"
                 rng.Characters(InStr(rng, " ") + 1).Font.Color = vbGreen
               
             Case "q"
                 rng.Characters(InStr(rng, " ") + 1).Font.Color = vbRed
                    
             Case "r"
                 rng.Characters(InStr(rng, " ") + 1).Font.Color = RGB(153, 255, 153)
         End Select
     End If
Next

End Sub

and gives me this

 

[sksanjeev786]

I have no idea what hollow green is and didn't find out by searching. If you don't like what I chose for a lighter green, then adjust the 153,255,153 values. In fact, you could tweak all of the colours if you use the RGB method rather than vbRed or vbGreen if you like, but I leave that to you.
This seems to work

Sub ColourNumbers()
Dim Lrow As Long, Lcol As Long, i As Integer
Dim rng As Range

With ActiveSheet
     Lrow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
     Lcol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End With

For Each rng In Range(Cells(1, 2), Cells(Lrow, Lcol))
     If Not rng Is Nothing Then
         rng.Characters(1, InStr(rng, " ")).Font.Color = vbBlack
         Select Case Right(rng, 1)
             Case "p"
                 rng.Characters(InStr(rng, " ") + 1).Font.Color = vbGreen
              
             Case "q"
                 rng.Characters(InStr(rng, " ") + 1).Font.Color = vbRed
                   
             Case "r"
                 rng.Characters(InStr(rng, " ") + 1).Font.Color = RGB(153, 255, 153)
         End Select
     End If
Next

End Sub

and gives me this
View attachment 86325

Hi Sir,

Thank you so much but i can not see #.0 in the data for example cell C6 and D8 (C6 should be 0.0 and D should be -1.0) and cell G8 9.0

 

[Micron]

I can't do any more for this. My code is based on your data and what you asked for. If the cell contains 0 and you want 0.0 then format your cells. As for G8 needing to be 9.0 when the data you supplied shows 9 then again, format your cells as you wish. All my code does is colour format your characters as you asked for. It doesn't alter formatting of cell values beyond that.