Conditional formatting using If

[John]

Hello All,

I am trying to keep track of peoples' email addresses that are not receiving
my messages. Currently, the cell range A1:L1 contains my headings, Cells
A2:A126 contain the Contact ID numbers. Currently I have 126 Contacts. Column
K is my status Column. I want to create aconditional format that will format
the entire row as follows:
____________________________________________________________________
If K1="n" (n meaning not functioning properl.)
then formatting of A2:K2 should be font= bold and fill= red
else
If K1="f" (f meaning funtioning properly)
then formatting of A2:K2 should be font= bold and fill= bright green
____________________________________________________________________
Once I have the proper formula do do this I should be able to copy the
formula into each row.
There is one other problem though. Column L contains a Description of the
problem the email account is experiencing. Therefore I have already tried to
set up a conditional format so that if the value in K2="n" the formatting for
L2 would be font color=red. However, it wouldn't work. So how do I get L2
formatted so that the font type will be Arial and so that the font color will
be red without changing the result of the conditional formatting for the cell
range of A2:K2?

Any suggestions would be greatly appreciated.

Thank you,

John R. L.

 

[Pete_UK]

I'm not sure why K1 should affect the formatting of row 2 - I presume
you mean that if K2="n" then set the format of A2:K2 appropriately.
Also, conditional formatting in L2 should not change that in A2:K2.

With that in mind, highlight the cells A2:K2 and click on Format |
Conditional Formatting. In the first box of the pop-up you should
select Formula Is rather than Cell Value Is, and then in the formula
box you should enter:

=$K2="n"

Click on the Format button, select the Font tab, and choose Bold, then
select the Patterns tab (for background colour) and choose Red, then
OK to return to the CF dialogue. Click on Add to set up your second
condition, choose Formula Is with the formula:

=$K2="f"

and then the Format button again to set your format of Bold and Bright
Green fill colour as before. Click OK twice to exit the CF dialogue
box. Then select cell L2 to set up the CF for that cell - click on
Format | Conditional Formatting, select Formula Is, enter this
formula:

=$K2="n"

and select Red from the Colour box of the Font tab, then OK twice. (Or
you might want to have a second condition here to have a bright green
font colour if K2="f", though you didn't specify this).

Now you can highlight all the cells A2:L2, click <copy>, move cursor
to A3 and highlight A3:A126, then click on Edit | Paste Special |
Formats | OK | <Esc> to apply those formats to your other cells.

Hope this helps.

Pete