Conditional formatting

[Nanaia]

Is there a formula that will enable me to use conditional formatting to highlight a cell containing data that has both V and N in it at the same time? I thought I could use an AND formula but am not sure how to arrange it. I have it set to highlight if it's just V, or just N, but want to make it a different color if it's both V AND N.

 

[Johnny Thunder]

This is just a sample but you could do something like

Formula:
=AND($A2="N",$A2>="V")

 

[Nanaia]

Thank you.
It won't highlight the cell the color designated for having both V and N, it is only highlighting with the color designated for cells that have V (which is the first to appear in the order of characters). Do I need to change the conditions for if the cell has just V or just N in it?

 

[Johnny Thunder]

Can you give a couple of example of cells that would have the criteria "V" or "N" or could it also be "NV" or "VN"

this is just a guessing game at this point.

 

[Nanaia]

"INT_V\1111_N//FaceX 12.500 FaceZ 6.000"

Sometimes it will have a "V" and not an "N", sometimes it has an "N" and not a "V". I've already made condition settings and colours associated for those, but am struggling with making one that has both a "_V" and an "_N". In lieu of "V" it could have an "H" ("INT_H\1111_N//FaceX 12.500 FaceZ 6.000"), it's either one or the other. The "N" is either added, or not there ("INT_V\1111//FaceX 12.500 FaceZ 6.000"). Excel seems to see the "V" and highlight the colour for that and goes no further. I've even tried changing the order the rules are applied but it doesn't seem to make a difference. It will highlight the cell the colour associated for only containing a "V", not what I selected for having both criteria.

Thanks for your help.

 

[stevieinselby]

This should work: conditional formatting > use a formula:
=IFERROR(FIND("N",A1)*FIND("V",A1)*0,1)=0

If cell A1 doesn't contain an 'N' and a 'V' then one or both of the FIND() functions will throw an error, so the IFERROR() will abandon ship and fall back to 1, which isn't =0 so the whole thing evaluates as false, and doesn't trigger the conditional format.

If cell A1 contains both an 'N' and a 'V' then both the find functions will resolve to numbers. If they are both numbers then you can multiply them together, and multiply that by zero, and IFERROR() will be happy to let that stand, giving the result of =0, which then triggers the conditional format.

 

[Nanaia]

That only works if I delete the other two conditions.
Using (=IFERROR(FIND("N",A1)*FIND("V",A1)*0,1)=0) as condition #1 , what do I need to change/add as condition criteria to so that if there's a "V" but not an "N" it's a different colour for condition #2 , or if there's an "N" and not a "V" it's a third colour for condition #3 ? As soon as I add my version of criteria for #2 , & #3 the cells that should be coloured for condition #1 change when I don't want them to because they don't meet the criteria. All three of these conditions can appear on the spreadsheet and I was trying to highlight the cells to reflect which criteria is there for quick visual assessment. I was trying to avoid adding a column for a note to appear to flag the entry.

Addendum: when using the suggested formula the cell will highlight even if there is not an "N" in it so it should meet criteria #2, not #1 and shouldn't be highlighting.

 

[Joe4]

Here is the CF formula that will check to see if it contains BOTH "_N" and "_V" (I think you want to check for the underscore along with the letter, otherwise it will find an "N" in "INT", which is not what you are looking for):

=AND(ISNUMBER(FIND("_N",A1)),ISNUMBER(FIND("_V",A1)))

Set this as your FIRST check, and check the both "Stop If True" option, so it does not continue on and check your other, lower priority checks.

 

[Nanaia]

Fabulous! It works! Thank you! I appreciate your assistance!

 

[Joe4]

You are welcome.