conditional formatting

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
If I select (say) B1:D4 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:D or the E:H range.

Does this help?

--
 
Hi Patricia,

Your formula should work if you select "Formula Is" rather than "Cell
Value Is equal to" in the conditional formatting dialogue box.
 
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.
 
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

--
 
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,
 
Thank you very much!

John James said:
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,
Click to expand...
 
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.
 
Back
Top