Unable to identify the greater number

[Alfredhigh]

Dear Sir/Mdm,



I used the "Conditional Formatting" to format the cell to greater than, but why the three cell(circle in red) didn't change color?

 

[Alfredhigh]

That is controlled by the format you have on that cell.
Just change it off the "Scientific" format to "General" or "Number", and it will appear the way you want.

View attachment 71922

What is the VBA code to convert "M" to six zero and "B" to nine zero?

 

[mikerickson]

The custom number format [<999999]0.00;[<99999900]0.00,," M";0.00,,," B" will display numbers in the format shown. (This uses a US billion)

 

[Alfredhigh]

The custom number format [<999999]0.00;[<99999900]0.00,," M";0.00,,," B" will display numbers in the format shown. (This uses a US billion)

How to inserted this VBA code?

 

[mikerickson]

That is a custom number format, not VBA code. If you format your cells with that format. It will take the numbers entered (as numbers) and display them in the format shown. Then the normal operations, like sorting, will act as expected.
The OP shows a sheet with text entries, which is the root of the problem. Changing the sheet values to numbers will correct those problems.

 

[Alfredhigh]

That is a custom number format, not VBA code. If you format your cells with that format. It will take the numbers entered (as numbers) and display them in the format shown. Then the normal operations, like sorting, will act as expected.
The OP shows a sheet with text entries, which is the root of the problem. Changing the sheet values to numbers will correct those problems.

Click where to do that?

 

[mikerickson]

In the Format Cell dialog box.

 

[Joe4]

I already provided you with the code last week up in post #6 in order to change the values to valid numbers.
You can simply add a line to change the cell format at the same time, i.e.

Sub ConvertNums()

    Dim cell As Range
    
    Application.ScreenUpdating = False
    
'   Change format of selected cells
    Selection.NumberFormat = "0"

'   Run against every cell in selected range
    For Each cell In Selection
        Select Case Right(cell, 1)
            Case "M"
                cell.Value = Left(cell, Len(cell) - 1) * 1000000
            Case "B"
                cell.Value = Left(cell, Len(cell) - 1) * 1000000000
        End Select
    Next cell
    
    Application.ScreenUpdating = True
        
End Sub

 

[Alfredhigh]

In the Format Cell dialog box.

I can't find this [<999999]0.00;[<99999900]0.00,," M";0.00,,," B" in the format cell dialog box.

 

[Alfredhigh]

I already provided you with the code last week up in post #6 in order to change the values to valid numbers.
You can simply add a line to change the cell format at the same time, i.e.

Sub ConvertNums()

    Dim cell As Range
   
    Application.ScreenUpdating = False
   
'   Change format of selected cells
    Selection.NumberFormat = "0"

'   Run against every cell in selected range
    For Each cell In Selection
        Select Case Right(cell, 1)
            Case "M"
                cell.Value = Left(cell, Len(cell) - 1) * 1000000
            Case "B"
                cell.Value = Left(cell, Len(cell) - 1) * 1000000000
        End Select
    Next cell
   
    Application.ScreenUpdating = True
       
End Sub

Hi Joe4, May I know add what line to change the cell format?

 

[Joe4]

Hi Joe4, May I know add what line to change the cell format?

If you look closely, and compare the code I posted in my last post to the code I posted previously, you will see that I already added it for you:

    Selection.NumberFormat = "0"

So all you need to do is copy & paste the code from my last post. Everything you need should be there.