Highlight Row if Cell Doesnt Contain Text

[JumboCactuar]

Hi,
ive tried this with conditional formatting but cant figure it
if B8 doesnt contain "Connected" then highlight row red

and wanting it to affect rows down to 100

any help appreciated

 

[Rick Rothstein]

Hi,
ive tried this with conditional formatting but cant figure it
if B8 doesnt contain "Connected" then highlight row red

and wanting it to affect rows down to 100

any help appreciated

For Conditional Formatting, select rows 1 thru 100 (fast way is to enter 1:100 in the Name Box next to the Formula Bar), press the Tab key once to move the Active Cell to cell B1, then call up the Conditional Formatting dialog box and create a New Rule using this formula...

=$B1<>"Connected"

 

[JumboCactuar]

For Conditional Formatting, select rows 1 thru 100 (fast way is to enter 1:100 in the Name Box next to the Formula Bar), press the Tab key once to move the Active Cell to cell B1, then call up the Conditional Formatting dialog box and create a New Rule using this formula...

=$B1<>"Connected"

i tried this but as soon as i apply the rule the entire area is filled (1:100) including rows containing "Connected"

 

[Rick Rothstein]

i tried this but as soon as i apply the rule the entire area is filled (1:100) including rows containing "Connected"

Is the word "Connected" the only word in the cell(s) or is it in there along with other text?

 

[JumboCactuar]

ADVERTISEMENT

Is the word "Connected" the only word in the cell(s) or is it in there along with other text?

yes nothing else in the cell and its formatted as general

ive tested by highlighting a row with "Connected" and doing the following:

which highlights the entire row except the cell with connected

 

[Rick Rothstein]

yes nothing else in the cell and its formatted as general

Are you sure there is nothing else in the cells besides the word "Connected"? Maybe you got a trailing space (ASCII 32) or non-breaking space (ASCII 160) in there by accident. Maybe put this in a blank cell to see (assuming cell B1 has the word "Connected" in it)...

=LEN(B1)

If it returns anything greater than 9, then you have one or more extra spaces in there. The reason I am pressing this so hard is because what I posted worked for me before I posted it and I just retested it again and it still works for me.

 

[JumboCactuar]

ADVERTISEMENT

Are you sure there is nothing else in the cells besides the word "Connected"? Maybe you got a trailing space (ASCII 32) or non-breaking space (ASCII 160) in there by accident. Maybe put this in a blank cell to see (assuming cell B1 has the word "Connected" in it)...

=LEN(B1)

If it returns anything greater than 9, then you have one or more extra spaces in there. The reason I am pressing this so hard is because what I posted worked for me before I posted it and I just retested it again and it still works for me.

my cell containing connected starts at D8 now all the way to D100 but i edited the formula you posted to reflect the changes and still isnt working

=LEN(D8) is returning 9

 

[JumboCactuar]

Bump
Anyone know how?

 

[Rick Rothstein]

my cell containing connected starts at D8 now all the way to D100 but i edited the formula you posted to reflect the changes and still isnt working

=LEN(D8) is returning 9

Any chance you could post a copy of the original workbook to OneDrive or DropBox so I (we) can examine your data directly and test the formula while watching it react to your data in order to determine why it works for me but does not work for you?

 

[BarryL]

When you entered Ricks Original rule. Can you go into Manage Rules and see that it is there correctly? there might be double quotes or rogue quotes or leading spaces in the rule.