VBA - find cells with number ending with...

[david_g]

Hi,

I do some student work - data entry (adresses of customers) into web application from Logistics company, which has option to export data into excel. Prices of all products ends with 0 (0.00 american way or 0,00 european way) - like 140, 320, 80... Because of large number of packages sometimes I make mistake typing prices into web application and then occurs problems with paying from customers. It occurs even if I look manually the excel sheet (from export) that I miss my mistakes regarding prices.

I have looked a lot of VBA forums and could not find VBA code for something like "find cell with number ending with...". So please help me.

I would like that macro will:
- find all cells in certain range (column, except first cell) with number ending not with 0 (e.g. find numbers ending with 1,2,3,4,5,6,7,8 or 9)
- highlight cells that meet the condition above (e.g. fill cell with red color)
- otherwise message box "Prices are ok" (e.g. all cells in range containing numbers ends with 0)

* export file has one column (E) with prices and first cell in this coumn is text "Price"

Thanks in advance

 

[JEC]

Run this macro on your file. Then filter column E on color "Red"

Sub jec()
 With Range("E2", Range("E" & Rows.Count).End(xlUp)).FormatConditions
    .Delete
    .Add 2, , "=--RIGHT($E2)=0"
    .Item(1).Interior.Color = vbRed
 End With
End Sub

 

[david_g]

First thanks for your reply and vba code

Unfortunatelly this code does exactly the opposite what I want. So it colours in red all cells in column E with values ending with 0. So please:
1) change code that it colours all cells in Coloumn E with values NOT ending with 0
2) tell me how I would change code if it comes, that also value ending with 5 is correct (so in this case cells values ending with 0 or 5 should not be colored with red)

Temporarily I tried to solve problem with making 9 macros /VBA codes changing this part "=--RIGHT($E2)=0". So I just changed 0 with numbers from 1 to 9. Then I have merged them into one macro using this advice:

 

[kevin9999]

Here's a variation on the code provided by @JEC in post #2. Assumes you run the code with the sheet active, and that the numbers are actual numbers - not text. To start including numbers ending in 5 as being "OK" (as per point 2 in post #3), change:

This line

If ar(i, 1) Mod 10 <> 0 Then j = j + 1

to this

If ar(i, 1) Mod 5 <> 0 Then j = j + 1

and this line

.Add 2, , "=MOD(E2,10)<>0"

to this

.Add 2, , "=MOD(E2,5)<>0"

Option Explicit
Sub david_g()
    Dim ar, i As Long, j As Long
    ar = Range("E2", Range("E" & Rows.Count).End(3))
    j = 0
    For i = 1 To UBound(ar)
        If ar(i, 1) Mod 10 <> 0 Then j = j + 1
    Next i
    
    If j > 0 Then
        With Range("E2", Range("E" & Rows.Count).End(3)).FormatConditions
            .Delete
            .Add 2, , "=MOD(E2,10)<>0"
            .Item(1).Interior.Color = vbRed
        End With
        Else
            MsgBox "Prices are ok"
    End If

End Sub