VBA - find cells with number ending with...

    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

    Why not use Conditional Formatting?


    Example file attached.


    Formula for Conditional Formatting


    =AND(LEN(E2)>0,RIGHT(E2,1)<>"0")


    and is applied to E2 to E200000 (change the 200000 if more than 200,000 rows of data likely)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login