Highlight a number cell based on the text contents of another cell / cells

DILIPandey

Well-known Member

Joined

Jul 25, 2013

Messages

1,336

• Mar 26, 2014

• #2

What is that 'certain' text which would be entered in column E and G ?
For column F, is there a fixed number or you just want to highlight and put any number of your choice ?


Regards,
DILIPandey

 

Upvote 0

L

looper

New Member

Joined

Mar 26, 2014

Messages

13

• Mar 26, 2014

• #3

There are some fixed words that i want to follow, one of them is "tir", other one is "cisterna".

Column F should be filled with any number from 1 to 16 or multiple ones, ex: "1" or "1, 2-14", so no, there's no fixed number.

 

Upvote 0

DILIPandey

Well-known Member

Joined

Jul 25, 2013

Messages

1,336

• Mar 26, 2014

• #4

Okay.. so can we say -> If column E is "tir" and column G is "cisterna" then column F should show "1".

Can you provide this kind of mapping else how Excel would understand what you want to show on what condition ?


Regards,
DILIPandey

 

Upvote 0

L

looper

New Member

Joined

Mar 26, 2014

Messages

13

• Mar 26, 2014

• #5

Nop, let me be more specific:

Let's leave out the E column, i don't think we'll need it eventually. The words "tir" and "cisterna" are just a part of the text entered in G, but they won't be entered on every row, just from time to time.

So, when Excel sees one of these words, as part of a phrase, in the G column, it should check to see is F is completed, if not, it should highlight it.

 

Upvote 0

DILIPandey

Well-known Member

Joined

Jul 25, 2013

Messages

1,336

• Mar 27, 2014

• #6

Okay... for that specific case, use below formula in conditional formatting in, let say, cell F4:-

=AND($F4="",SEARCH("tir",$G4))

and format fill as yellow color.

Now F4 will be highlighted if G4 contains "tir" anywhere as a part of entered text string and F4 itself is left blank.

This is what we have done for a specific 'tir' - but you must be having more requirements related with other such strings like "cisterna" etc and that is what I asked in post $4 OR you want same thing to happen with all of such strings ? Thanks.


Regards,
DILIPandey

 

Upvote 0

L

looper

New Member

Joined

Mar 26, 2014

Messages

13

• Mar 27, 2014

• #7

I have changed the cell number from 4 to 77, where i am right now, but after inserting the formula in the "Format values where formula is true" window, i get an error saying that the formula is incorrect.

My question is, can the formula be applied in cells where there's no text? I want it to highlight the following entries, not the ones already there.

In response to the question from post #4: for all the specific words like "tir" and "cisterna", i want the same thing to happen, highlight the F cell if its left blank, with the same formating rules.

 

Upvote 0

DILIPandey

Well-known Member

Joined

Jul 25, 2013

Messages

1,336

• Mar 27, 2014

• #8

Not sure if you changed 4 to 77 for both the cells i.e., F and G.
Okay.. lets make it simple and straight forward:-

You want to highlight column F, IF column F is blank AND column G contains any one of the following as a part of text :-
1) tir
2) cisterna
3) ..
4) ..

complete the list and let me know.. thanks.


Regards,
DILIPandey

 

Upvote 0

L

looper

New Member

Joined

Mar 26, 2014

Messages

13

• Mar 27, 2014

• #9

Ok, let's go with these words for now:

1) tir
2) cisterna
3) cantar
4) descarcare
5) usa
6) barca

But, here's another idea: is it possible to not highlight the F column when just 2 words are in the G column and just highlight it if these 2 words ("verificare" "rond") are not there?

This way, we'll minimize the words to check list, because these are not fixed and may vary from time to time.

 

Upvote 0

DILIPandey

Well-known Member

Joined

Jul 25, 2013

Messages

1,336

• Mar 27, 2014

• #10

Okay.. I guess another idea is good.
Use below formula in the conditional formatting for cell F1 and highlight the format as yellow. Now copy down the conditional formatting.


Regards,
DILIPandey

 

Upvote 0