conditional formatting help please

[*k*]

greetings......i have a row in which if say a4 contains the word help,
then all that row is in red text, othewise it is in black text

a1 a2 a3 a4 a4
dept author title help low


so that if a4 says help..... help me or help now etc....... the whole
row is red


thanks in advance
K

 

[Leo Heuser]

Hello

1. Select row 4 and choose Format > Conditional formatting
2. Choose "Formula is" and enter this formula:
=SEARCH("help",$A$4)
It's imperative that A4 is absolute (contain $$). If not, it will
change through the row: A4, B4, C4 etc.
3. Choose red text. OK. OK.

Now, if A1 contains the word "help" (among other words or
alone), row 4 will contain red text.

 

[Guest]

what do you want to appear in red? just the word help when it stands alone/

 

[David McRitchie]

Hi Karen,
I have a page on Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm

If instead of just row 4 with Help in column A, you wanted
to highlight all rows that had Help in Column A, you would
Select all cells before entering the formula, then with A1 as
the active cell:
format, cells, Conditional Format
formula is: =$A1="help"
then format the font Red from the big format button.

 

[Domenic]

Hi,

Select your data
Format > Conditional Formatting > Formula Is
=SEARCH("help",$A1)
Choose your formatting
Click Ok

Hope this helps

 

[*k*]

easy when ya know how....thanks for that

 

[*k*]

sorry no....the a1 to a5

 

[*k*]

Hi Davis, sorry I did have a look at your excellent site...but didnt
see what I was after.....obviously not looking to closely was
I.....thanks for that

 

[*k*]

sure does..... thanks for that much appreciated

 

[Leo Heuser]

You're welcome. Thanks for the feedback

LeoH

 

[David McRitchie]

Hi Karen,
Perhaps "help" was not the only content in those cells, as that would make
=SEARCH("help",$A1)
work where the following would fail
=$A1="help"

The formula specified is relative to the active cell, so the C.F.
will only select the correct rows if a cell on the first row is
the active cell when entering the formula.
---

 

[*k*]

Hi Karen,
Perhaps "help" was not the only content in those cells, as that would make
=SEARCH("help",$A1)
work where the following would fail
=$A1="help"

The formula specified is relative to the active cell, so the C.F.
will only select the correct rows if a cell on the first row is
the active cell when entering the formula.

sorry i wasnt very clear was I......... <blush>

what i have is

a b c d e
1name dept enqiry type priority
2
3 help
4 help

so that if d1 = help then a1 to e1 are red

so in this case lines 3 and 4 cells a to e would be red

hope that is better ?

thanks
K

 

[Ragdyer]

If I understand what you're looking for, try this:

Labels in row 1, from A1 to E1.
Now, select A2:E2,
Where the focus is in A2 (white) and the rest are black.
Then,
<Format> <ConditionalFormat>
Click "FormulaIs",
And enter this formula:
=SEARCH("help",$D2)

Set your format as you wish to red,
Then <OK> <OK>

NOW ... Hold <Ctrl> and hit <Enter>.

You should now have your formula entered in the five cells in row 2.

Enter some test data to make sure that row 2 is working as you wish.
When you're satisfied that it's working correctly, clear the test data so
that row 2 is now empty again.

Now, select A2:E2 all over again.
With the five cells selected, click the fill handle at the bottom right of
E2, and drag down the entire selection, to copy as far as needed.

Hopefully, you now have what you're looking for.

 

[David McRitchie]

Hi Karen,
Select A1, Ctrl+A to select all cells
Formula is based on the active cell, and since the
Column is absolute -- just need any cell on Row 1 as the active cell.

Format, Conditonal Formatting,
Formula is: =$D1="help"
format button pattern -- red

Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm