[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 337
  • Last Modified:

Hilight item numbers with a -04 suffix

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
3 Solutions
Use conditional Formating with formula (use formula to determine which cell to format)
=RIGHT(TEXT($A1," ##-###-##-##"),2)="04"
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.
sample attached...
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

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

AndreasHermleAuthor Commented:
Dear all,

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

Regards, Andreas

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now