conditional formatting

[Guest]

How can I set conditional formatting for a row when the value in the first
cell of the row contains data? I have tried the following formulas, and
neither does anything, even when it's the only conditional formatting.

=($A1<>"")
=(A1<>"")

Thanks to your help to other people on formatting every other row, I am able
to format rows 4,8,12, ... one color, and rows 2,6,10 another color. By
playing around, I found that the formatting occurs when the value is TRUE or
1, so I don't understand why one of the above formulas doesn't work.
Thank you.

 

[Bryan Hessey]

If I select (say) B14 and enter (in Format, Conditional format
Formula is)

=$A1<>""

then select E1:H4 and enter

=$A1=""

and set the conditional format, then I have that format displayed i
either the B or the E:H range.

Does this help?

--

 

[John James]

Hi Patricia,

Your formula should work if you select "Formula Is" rather than "Cell
Value Is equal to" in the conditional formatting dialogue box.

 

[Guest]

Thanks. I finally got it to work by choosing the whole table by clicking on
the "cell" that is above the 1st row and left of the 1st col.

When I tried to select the whole sheet with CTRL+A, I got weird results of
coloring; also, in this case, when I would go back to the conditional format,
it had changed, and as I was looking at it, if I accidently moved the mouse,
the cursor would move around the workbook and change the formula.

However, I still have the problem that if I try to change the formula, it
replaces the formula with a cell address, and I have to totally retype the
formula if I want to change it.

 

[Guest]

Thanks.
I did choose formual. See my new msg above for how I got it to work.

 

[Bryan Hessey]

Patrticia,

When trying to edit a Conditional formula it is advisable to not use
the arrow keys, click to the point you wish to type and use Delete or
just re-type.

The 'conditional' works whether you select a single cell, a row or
column, or, as you have, the whole sheet.

Good to see it worked for you

--

 

[John James]

Hi Patricia,

To explain the weird results:

1. The formula you type is relative to your cursor location. e.g. If
the cursor is in, say, cell A2 and you press Ctrl-A, the worksheet is
selected BUT the cursor remains in A2, which is in row 2. When you
then enter =$A1<>0 in the conditional format, excel looks at the value
in A1 to determine the formatting for row 2!!! However, if the cursor
was in cell A1 when you pressed Ctrl-A, it would have worked. You'll
notice when you select the entire sheet with the mouse, the cursor
actually jumps to cell A1 - and that's why it worked when the sheet was
selected with the mouse, but didn't work when you pressed Ctrl-A.

2. If you want to edit the formula in the conditional formatting entry
area, then press the F2 key to change to edit mode.

Cheers,

 

[Guest]

Thank you very much!

Hi Patricia,

To explain the weird results:

1. The formula you type is relative to your cursor location. e.g. If
the cursor is in, say, cell A2 and you press Ctrl-A, the worksheet is
selected BUT the cursor remains in A2, which is in row 2. When you
then enter =$A1<>0 in the conditional format, excel looks at the value
in A1 to determine the formatting for row 2!!! However, if the cursor
was in cell A1 when you pressed Ctrl-A, it would have worked. You'll
notice when you select the entire sheet with the mouse, the cursor
actually jumps to cell A1 - and that's why it worked when the sheet was
selected with the mouse, but didn't work when you pressed Ctrl-A.

2. If you want to edit the formula in the conditional formatting entry
area, then press the F2 key to change to edit mode.

Cheers,

 

[Guest]

Thank you very much. Your original post, about selecting different areas of
t he worksheet for different conditional formatting was also helpful, as I
didn't really realize we could do different conditional formatting for
different areas. I have just started using conditional formatting, and what
I had done so far was using mod(row()... , which doesn't seem to work the
same with the selected area. I have a macro that worked fine with
highlighting every 4th row, but when I added the condition that I asked
about, involving $a1, I had to add a Select to my macro.

 

[Bryan Hessey]

Good to see it works for you, and thanks for the response

--

 

[John James]

It's a buzz helping people like you, Patricia, who show appreciation and
give feedback.