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

 

[6StringJazzer]

I'm not following your problem. Why don't you go on the worksheet and format the whole columns to have 1 digit after the decimal point?

Also InStr returns a number. Why are you using Trim on it?

 

[Peter_SSs]

@sksanjeev786
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[bebo021999]

May be this helps:

Sub ColourNumbers()
Dim i&, j&, s, rng As Range, cell As Range
With Sheets("Sheet1")
    Set rng = Range("B1").CurrentRegion
    For i = 1 To rng.Rows.Count
        For j = 2 To rng.Columns.Count
            Set cell = rng.Cells(i, j)
            If IsNumeric(cell) Then
                cell.Font.Color = vbBlack
            Else
                s = Split(cell) '==> cell = "6.0 p" then s(0) = 6.0; s(1) = "p"
                cell.Characters(1, Len(s(0))).Font.Color = vbBlack ' change 6.0 color
            End If
            Select Case j
                Case 3, 4, 5, 7 ' case column C,D,E,G
                    cell.NumberFormat = "0.0"
            End Select
        Next
    Next
End With
End Sub

 

[sksanjeev786]

May be this helps:

Sub ColourNumbers()
Dim i&, j&, s, rng As Range, cell As Range
With Sheets("Sheet1")
    Set rng = Range("B1").CurrentRegion
    For i = 1 To rng.Rows.Count
        For j = 2 To rng.Columns.Count
            Set cell = rng.Cells(i, j)
            If IsNumeric(cell) Then
                cell.Font.Color = vbBlack
            Else
                s = Split(cell) '==> cell = "6.0 p" then s(0) = 6.0; s(1) = "p"
                cell.Characters(1, Len(s(0))).Font.Color = vbBlack ' change 6.0 color
            End If
            Select Case j
                Case 3, 4, 5, 7 ' case column C,D,E,G
                    cell.NumberFormat = "0.0"
            End Select
        Next
    Next
End With
End Sub

Super!!! Perfect!!!!

Thank you Bebo for your time and help on this

 

[sksanjeev786]

@sksanjeev786
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Sure thank you so much, Peter for your guidance on this sure I will click on VBA Code

Hope after clicking on the VBA code i need to paste my VBA

"
Sub ColourNumbers()
Dim i&, j&, s, rng As Range, cell As Range
With Sheets("Sheet1")
Set rng = Range("B1").CurrentRegion
For i = 1 To rng.Rows.Count
For j = 2 To rng.Columns.Count
Set cell = rng.Cells(i, j)
If IsNumeric(cell) Then
cell.Font.Color = vbBlack
Else
s = Split(cell) '==> cell = "6.0 p" then s(0) = 6.0; s(1) = "p"
cell.Characters(1, Len(s(0))).Font.Color = vbBlack ' change 6.0 color
End If
Select Case j
Case 3, 4, 5, 7 ' case column C,D,E,G
cell.NumberFormat = "0.0"
End Select
Next
Next
End With
"
Let me know if i am doing correct way or incorrect way

 

[sksanjeev786]

I'm not following your problem. Why don't you go on the worksheet and format the whole columns to have 1 digit after the decimal point?

Also InStr returns a number. Why are you using Trim on it?

Got the solution for this.

Thanks for checking on this

 

[6StringJazzer]

Hope after clicking on the VBA code i need to paste my VBA

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.

 

[sksanjeev786]

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.

Got it!!! thank you so much

 

[sksanjeev786]

May be this helps:

Sub ColourNumbers()
Dim i&, j&, s, rng As Range, cell As Range
With Sheets("Sheet1")
    Set rng = Range("B1").CurrentRegion
    For i = 1 To rng.Rows.Count
        For j = 2 To rng.Columns.Count
            Set cell = rng.Cells(i, j)
            If IsNumeric(cell) Then
                cell.Font.Color = vbBlack
            Else
                s = Split(cell) '==> cell = "6.0 p" then s(0) = 6.0; s(1) = "p"
                cell.Characters(1, Len(s(0))).Font.Color = vbBlack ' change 6.0 color
            End If
            Select Case j
                Case 3, 4, 5, 7 ' case column C,D,E,G
                    cell.NumberFormat = "0.0"
            End Select
        Next
    Next
End With
End Sub

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