EXCEL - Highlight row based on text in a cell 1

[French79]

I know that this has been several thousand times but I can't find the answer for what I'm trying to do.

I'm able to highlight the cell based on text if I choose that cell only. I want to check every row and highlight the row if PIC S9 is anywhere in that cell on that row. The data that I want to check is only in the A column but I have a variable number of row per worksheet.

Thanks in advance

 

[AnotherHiggins]

Click on the square to the left of column A and above row 1 to select the entire spreadsheet.

Use a formula for the Conditional Formatting and make sure to use Absolute Reference for the column.

Example:

= $A1 = "PIC S9"

The dollar sign in front of the A indicates that only column A is going to be checked. The absence of a dollar sign in front of the 1 means that each row will be checked independently.


[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[French79]

I tried what you suggested and nothing happened. I choose "Use a formula to determine which cells to format"
add this in the formula =$A1="PIC S9" and then selected the color that I wanted. It took the formula but didn't highlight any of the rows where PIC S9 is in the cell

 

[randy700]

The formula provided by AnotherHiggins will work if the cell contains exactly PIC S9.
Not sure how to enter the formula if you need it when any part of the cell is equal to PIC S9.


Randy

 

[blister911]

What about this for the formula?

=IF(ISNUMBER(FIND("PIC S9",$A1,1)),"TRUE","FALSE")


Light travels faster than sound. That's why some people appear bright until you hear them speak.

 

[French79]

I figured it out by using the following

=IF(ISERR(FIND("PIC S9",A1,1)),FALSE,FIND("PIC S9",A1,1))

Thanks for your help

 

[AnotherHiggins]

Sorry, I missed, "...PIC S9 is anywhere in that cell...".

But a few notes: There's no need to use an IF statement. That's just overcomplicating things.

Also, I nearly always use SEARCH instead of FIND. SEARCH functions exactly the same way except that it isn't case-sensitive and therefore you're less likely to miss something if, for whatever reason, a record contains lower case letters.

blister911: There's no reason to wrap TRUE and FALSE in double quotes. You aren't trying to return text strings.

Finally, you don't even need to verify that SEARCH does return a number or doesn't return an error.

Any non-zero number will be construed as TRUE. And only rows where the formula is TRUE will get highlighted, so the #Value! errors are ignored.

That means that all you need is:

[tab][COLOR=blue white]=Search("PIC S9", $A1)[/color]

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[blister911]

AnotherHiggens,
My Excel skills are not near what some of you possess. I figured if the formula worked for French79 and there was a way to improve upon it, someone would bring it up. The quotes were there, because I returned them on the spreadsheet when I tested it (I suppose I could have used 1 and 0). Then, I just copied it to this thread.

Anyway, thanks for the update to the formula. I will keep it in mind if I ever need to do something like this.


Light travels faster than sound. That's why some people appear bright until you hear them speak.

 

[AnotherHiggins]

blister911:

Happy to help out. I'm just trying to share knowledge.

I've learned *so* much from this place and the many knowledgeable folks who donate their time here.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.