VBA needs to add ".0" (as 1.0)

[sksanjeev786]

Hi Team,

I have been using the below macro recently which change the color as per my requirement and everything works perfectly but i need to add .0 if we have a single decimal column C,D,E and G with yellow highlighted.

Can anyone help me with this?

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

With Sheets("Sheet1") '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

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

 

[Peter_SSs]

First you paste in the code, then you select what you just pasted, then you click the VBA button in the edit toolbar to apply code tags.

That is one way. I find it easier to insert the code tags first so this appears
[CODE=vba][/CODE]
and then just paste my code between those two tags, saves selecting the code again after pasting it.

 

[bebo021999]

Hi Bebo,

once again thank you so much for your help on this.

just wanted to check can we add "+" symbole (+0.8, +3.6 and for 0.0 it will be as 0.0 ) only and "-" data stay same..

Regards
Sanjeev

Could you put full representative samples into mini sheet then upload again?

 

[sksanjeev786]

Could you put full representative samples into mini sheet then upload again?

Hi Bebo,

For now i have only added in column "C"

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

 

[bebo021999]

The code still works for me. See before and after screenshot

 

[sksanjeev786]

The code still works for me. See before and after screenshot

View attachment 86915

yes the code is working perfectly for us i just wanted to add "+" symbol before the number, for now, we have "-" symbol in the negative score so i need to add "+" were ever we have a positive number..

Hope this make sense