Help needed with 'Conditional Formatting'

**aide199** · 08-08-2017, 02:55 PM

Hi Guys,

I've just created the attached spreadsheet but need some help setting up 'Conditional Formatting' in it.

What I need for it to do, is when part of a reference number is input into a cell, I need that cell to turn black.

They way I've set it up so far is when the start of a reference number is entered for example JDD0022 the cell turns black. But what I need for it to do is when the whole of that Reference number is entered, I need the cell to turn black but I can't work out how you do that. So what I'm trying to ask is for it to do a partial search 'the start of the reference number' then turn black after the full reference number has been entered.

The start of all the reference numbers can be found on 'Sheet1' of the attached spreadsheet

I hope this makes sence in what I'm asking for.

I've attached the spreadsheet. I hope someone can please help.

Thanks in advance*

**mehmetcik** · 08-08-2017, 03:18 PM

Use this conditional format formula for column C

Formula:

Please Login or Register  to view this content.

**aide199** · 08-08-2017, 03:24 PM

HI,

Thanks for your reply, So I've changed the formula to the one you suggested but it doesn't seem to work (or I'm not doing it right)

If I put the full 13 digit reference number in for example JD00022123450 the cell doesn't turn black but it will If I just enter in JD00022

I also need the same conditional formatting for all the reference numbers on sheet 1

Any ideas?

**mehmetcik** · 08-08-2017, 03:51 PM

The formula is looking for JD00022

**aide199** · 08-08-2017, 03:54 PM

So how do I set the formula so it finds JD00022 (or any of the others listed) when the full reference number is input (always 13 digits) and then turn the cell black?

Thanks in advance

**JeteMc** · 08-08-2017, 05:56 PM

Modify the conditional formatting formula, still applied to $C:$C, so that it reads:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**mehmetcik** · 08-08-2017, 08:56 PM

Ok this seems to work:

Conditional format for column C

Formula:

Please Login or Register  to view this content.

**aide199** · 08-09-2017, 02:24 AM

Many Thanks for your replies

I've tested both of the above shown Formula but it only black's out the cell if I input a reference number that starts with JD00022 - it doesn't work for any of the others shown on Sheet1

**mehmetcik** · 08-09-2017, 03:32 AM

Try again.

Formula:

Please Login or Register  to view this content.

**aide199** · 08-09-2017, 03:54 AM

Thanks but now none of reference numbers make the cell turn black

**mehmetcik** · 08-09-2017, 04:12 AM

Maybe

Please Login or Register  to view this content.

**aide199** · 08-09-2017, 04:15 AM

No, still not working

**mehmetcik** · 08-09-2017, 04:38 AM

My Mistake

Formula:

Please Login or Register  to view this content.

**AliGW** · 08-09-2017, 04:40 AM

Aide199 - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

**aide199** · 08-09-2017, 05:03 AM

Many Thanks with your patience with this but still not working

**JeteMc** · 08-09-2017, 09:09 AM

The conditional formatting formula from post #6 is applied to the attached file.
The values in rows 2 and 4 meet the specifications and are blacked out. The values in rows 3 and 5 are intentional mistypes.
Let us know if you have any questions.

**aide199** · 08-09-2017, 09:48 AM

@JeteMc - Thank you so much, that's now working perfectly. I really appreciate your efforts.

Thanks again

**JeteMc** · 08-09-2017, 10:12 AM

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

Help needed with 'Conditional Formatting'

LinkBack

Thread Tools

Rate This Thread

Display

Help needed with 'Conditional Formatting'

Re: Help needed with 'Conditional Formatting'

Re: Help needed with 'Conditional Formatting'

Re: Help needed with 'Conditional Formatting'

Re: Help needed with 'Conditional Formatting'

Re: Help needed with 'Conditional Formatting'

Re: Help needed with 'Conditional Formatting'

Re: Help needed with 'Conditional Formatting'

Re: Help needed with 'Conditional Formatting'

Re: Help needed with 'Conditional Formatting'

Re: Help needed with 'Conditional Formatting'

Re: Help needed with 'Conditional Formatting'

Re: Help needed with 'Conditional Formatting'

Re: Help needed with 'Conditional Formatting'

Re: Help needed with 'Conditional Formatting'

Re: Help needed with 'Conditional Formatting'

Re: Help needed with 'Conditional Formatting'

Re: Help needed with 'Conditional Formatting'

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Conditional Formatting help needed

Conditional formatting help needed

[SOLVED] Conditional Formatting Help Needed Please

Conditional Formatting - help needed

[SOLVED] Help Needed on Conditional Formatting

Conditional formatting needed for shape colour - is programming needed?

Bookmarks

Bookmarks

Posting Permissions