Hilight item numbers with a -04 suffix

Posted on 2011-05-04
Last Modified: 2012-05-11
Dear Experts:

I got a column with more than 16.000 item numbers. They all have the following make-up:

Example: 15-708-04-07, i.e. ##-###-##-##

I would like to highlight those (yellow cell fill) where the last two digits are -04.

The solution could be either conditional formatting, VBA or whatever.

Thank you very much in advance for your professional and kind help.

Regards, Andreas
Question by:AndreasHermle
    LVL 9

    Accepted Solution

    Use conditional Formating with formula (use formula to determine which cell to format)
    =RIGHT(TEXT($A1," ##-###-##-##"),2)="04"
    LVL 12

    Assisted Solution

    The '-' presents a problem unless it is always there. In that case it may be ignored and just look for the '04' at the end.

    I used conditional formatting.

    1. Select Range

    Select or name the range of cells to format and then select the name.

    2. Click Conditional Formatting

    In ribbon bar

    3. Select 'Manage Rules'

    I prefer this over the canned options.

    4. Click 'New Rule'

    5. Select 'Format only cells that contain'

    6. Pull down 'Cell Value'

    And select 'Specific Text'

    7. Pull down 'containing'

    And select 'ending with'

    8. Enter ending text in next field

    '04' without the quotes

    9. Click OK, OK

    Sample attached.
    LVL 9

    Expert Comment

    sample attached...
    LVL 50

    Assisted Solution


    if the cell content is


    it will be text, unless you have employed a custom number format to display a number with the dashes. In any case, you can implement a conditional format. Select all cells and the click Conditional formatting > New Rule > Use a formula to determine ... with a formula like


    Select a font color or fill color and click OK.

    The above formula applies if your cell range starts at A1. Amend it to suit your file. See attached.

    cheers, teylyn


    Author Closing Comment

    Dear all,

    thank you very much for your professional help. All three work just fine (teylyn's and chwong's are similar).

    Regards, Andreas

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now