Help with VBA to check for numbers after a decimal point

[philwojo]

Hello, I had gotten help on this in the past, but I am working on another project that is similar but I am having a hard time adapting the code to work.

First, here is the code I have as of now.

Sub HighlightIfNotTwoDecimalPoints()  Dim R As Long, c As Long, X As Long, StartRow As Long, StartCol As String, S() As String
  Dim ws As Worksheet, WS1 As Worksheet, WS2 As Worksheet, Data As Variant
  
Application.EnableEvents = False
Application.ScreenUpdating = False


'Checks specific cells associated with cash values to make sure that they have two zeros
'After a decimal place for values like $5.00, it also checks to make sure that there is a leading
'Zero if a cash value is less than $1, like $0.74, and if it finds those are missing
'It will highlight the cell red and turn the font color to yellow to flag the problem.


  ' Process cells on "PIF Checker Output - Horz" sheet
  StartRow = 23
  StartCol = "ZZ"
  Set ws = Sheets("PIF Checker Output - Horz")
  Data = Range(ws.Cells(StartRow, StartCol), ws.Cells(Rows.Count, StartCol).End(xlUp))
  For R = 1 To UBound(Data)
    S = Split(Trim(Data(R, 1)), "|")
    For X = 0 To UBound(S)
      If InStr(S(X), ".") = 0 Or S(X) Like "*." Or S(X) Like "*.#" Or S(X) Like "*.*.*" Or S(X) Like "*[!0-9.()]*" Or ("X" & S(X) Like "*[!0-9].#*") Then
        With ws.Cells(StartRow + R - 1, StartCol)
          .Interior.Color = vbRed
          With .Characters(InStr("|" & .Value & "|", "|" & S(X) & "|"), Len(S(X))).Font
            .Bold = True
            .Color = vbYellow
          End With
        End With
      End If
    Next
  Next
  
Application.EnableEvents = True
Application.ScreenUpdating = True


End Sub

What I need it to do is now slightly different, the sheet name is the same, but now I need it to first check column-B and then only do checks if the value in the cell of column-B is either 2100 or 3000.

But Where I need it to check varies for those two. If it there is a 2100 in column-B, then I need to check the cell in column-F.

If it is a 3000 in column-B I then need it to check for the cell in column-F & J & K.

One other wrinkle is that I can now also have negative values in the cells being checked, meaning the value in columns-F, J or K could be 5.00 or -5.00.

Ultimately I want to check and make sure that if there criteria is met then I check the cell to make sure that it has a decimal point and that decimal point is followed by 2 numbers.

I hope this all makes sense, and I appreciate any help anyone can provide.

Here is some sample data you can use as well:

1000,1.0,RLZSUA1,02022018092900,
2100,1.0,SPIF41,PG001,40.00,USD,01262018,02262108,950,0.00,0.00,,,,,YES,,,,,8000575,1,1,,,,,,,,,,,,,,,,
3000,1.0,SPIF41,1,45645a,10302017,,5.00,,5.00,testing comments,,,,
3000,1.0,SPIF41,2,45645b,10302017,,5.00,,5.00,testing comments,,,,
3000,1.0,SPIF41,3,45645c,10302017,,5.00,,5.00,testing comments,,,,
3000,1.0,SPIF41,4,45645d,10302017,,5.00,,5.00,testing comments,,,,
3000,1.0,SPIF41,5,45645e,10302017,,5.00,,5.00,testing comments,,,,
3000,1.0,SPIF41,6,45645f,10302017,,5.00,,5.00,testing comments,,,,
3000,1.0,SPIF41,7,45645g,10302017,,5.00,,5.00,testing comments,,,,
3000,1.0,SPIF41,8,45645h,10302017,,5.00,,5.00,testing comments,,,,
2100,1.0,SPIF42,PG001,555.00,USD,01262018,02262108,950,,,,,,,YES,Notes,,,,8000720,1,1,,,,,,,,,,,,,,,,
3000,1.0,SPIF42,1,163,11012017,,500.00,,500.00,,,,,
3000,1.0,SPIF42,2,936,11052017,,5.00,,5.00,,,,,
2100,1.0,SPIF43,PG001,250.00,USD,01262018,02262108,950,,,,,250.00,250.00,YES,Notes,,,,8000720,1,1,,,,,,,,,,,,,,,,
3000,1.0,SPIF43,1,741,11052017,,250.00,,250.00,,,,,
2100,1.0,SPIF50,PG001,95.00,USD,,02262018,950,,,,,,,YES,,,,,,,,,,Elmer Fudd Rabbit Farm,ron.verdas@test.com,,,,,,,,,,,,
3000,1.0,SPIF50,63,11012017,,100.00,,100.00,,,,,
3000,1.0,SPIF50,2,13,11012017,,-5.00,,-5.00,,,,,
5000,4,,

Thanks,
Phil

 

[philwojo]

I know this is an old thread, but I am now working on this item again, it had been put on the back burner for a while.

Anyone have any feedback on how to update the code I posted?

Also, one update to the above notes, if it is a 2100 line then it would need to check columns F, O and P.

Thanks for any help,
Phil