Convert a function to Sub procdure

[Bablu]

Hello All,

I am trying to convert a function into a sub procedure. The function below works great, but for me it would be real use if I could use as a Sub procedure.

Basically, I would want to select the range and if after the decimal if its not rounded to 2 numbers then I would want to highlight.

Basically something like this:

1.2 <- highlight cell
1.20 <- do not highlight the cell
1.3232 <- highlight the cell

Function GetNumberDecimalPlaces(theCell As Range) As Integer
Dim periodPlace As Integer, stringLength As Integer
periodPlace = InStr(1, theCell.Text, ".")
If periodPlace = 0 Then
GetNumberDecimalPlaces = 0
Else
stringLength = Len(theCell.Text)
GetNumberDecimalPlaces = stringLength - periodPlace
End If
End Function

Any help would be great.

Thanks in advance.

 

[JoeMo]

Try:

Sub GetNumberDecimalPlaces()
'Select the cells first, then run this macro
Dim c As Range
For Each c In Selection
    If InStr(1, c.Value, ".") > 0 Then
        If InStrRev(c.Value, ".") <> Len(c.Value) - 2 Then c.Interior.Color = vbYellow
    End If
Next c
End Sub

 

[DanteAmor]

You could use conditional formatting.
Select your cells. Put the following formula. Select the color to highlight.

=LEN(MID(B2,FIND(".",B2&".")+1,99))<>2

1.20 <- do not highlight the cell

1.20 does not exist in excel, because the zero to the extreme right after the decimal point has no value. Actually in excel you have 1.2
So the formula should be> 2 or <1, because 1.2 is equal to 1.20 or 1.5 = 1.50, etc.

 

[Bablu]

You could use conditional formatting.
Select your cells. Put the following formula. Select the color to highlight.

=LEN(MID(B2,FIND(".",B2&".")+1,99))<>2

1.20 does not exist in excel, because the zero to the extreme right after the decimal point has no value. Actually in excel you have 1.2
So the formula should be> 2 or <1, because 1.2 is equal to 1.20 or 1.5 = 1.50, etc.

Thank you, DanteAmor. I will try your suggestion as well to increase my understanding, but macro makes the process much better for this purpose.

 

[Bablu]

Try:

Sub GetNumberDecimalPlaces()
'Select the cells first, then run this macro
Dim c As Range
For Each c In Selection
    If InStr(1, c.Value, ".") > 0 Then
        If InStrRev(c.Value, ".") <> Len(c.Value) - 2 Then c.Interior.Color = vbYellow
    End If
Next c
End Sub

JoMoe, thank you for quick turnaround. It works very well. Just a little minor issue.

Below is the outcome by the macro on my file:

35.90 <- this was highlighted, didn't need to be highlighted, because this is proper presentation as there is 2 decimal places.
54.94 <- this was not highlighted, which is what I expected, but its inconsistent with the above.
.10 <- this was highlighted, this does not need to be highlighted because there is a 2 decimal places.


a bit more help?

Thanks again.

 

[Phuoc]

Try this:

Sub GetNumberDecimalPlaces()
Dim c As Range
For Each c In Selection
        If Len(Split(c.Text & ".5555", ".")(1)) <> 2 Then c.Interior.Color = vbRed
Next c
End Sub

 

[Rick Rothstein]

Does this macro work for you (select the cells you want to process and then run the macro)...

Sub FindNotTwoDecimalDigitsInSelection()
  Dim Cell As Range
  For Each Cell In Selection
    If Not Cell.Text Like "*.##" Then Cell.Interior.Color = vbYellow
  Next
End Sub

 

[Bablu]

Does this macro work for you (select the cells you want to process and then run the macro)...

Sub FindNotTwoDecimalDigitsInSelection()
  Dim Cell As Range
  For Each Cell In Selection
    If Not Cell.Text Like "*.##" Then Cell.Interior.Color = vbYellow
  Next
End Sub

Rick, yes, this works. Thank you!

 

[Bablu]

Try this:

Sub GetNumberDecimalPlaces()
Dim c As Range
For Each c In Selection
        If Len(Split(c.Text & ".5555", ".")(1)) <> 2 Then c.Interior.Color = vbRed
Next c
End Sub

Thank you, Phuoc. This macro works as well.

 

[JoeMo]

JoMoe, thank you for quick turnaround. It works very well. Just a little minor issue.

Below is the outcome by the macro on my file:

35.90 <- this was highlighted, didn't need to be highlighted, because this is proper presentation as there is 2 decimal places.
54.94 <- this was not highlighted, which is what I expected, but its inconsistent with the above.
.10 <- this was highlighted, this does not need to be highlighted because there is a 2 decimal places.


a bit more help?

Thanks again.

This should fix the problem you cited.

Sub GetNumberDecimalPlaces()
'Select the cells first, then run this macro
Dim c As Range
For Each c In Selection
    If InStr(1, c.Text, ".") > 0 Then
        If InStrRev(c.Text, ".") <> Len(c.Text) - 2 Then c.Interior.Color = vbYellow
    End If
Next c
End Sub