Conditional Formatting ROW not working

[Rod]

I'm having a weird problem. I'm simply trying to do a conditional format on a
row, for example: If the value of cell A1 is 100, then highlight cells B1:B5
with yellow.

I go through to motion of selection B1 through B5, select conditional
formatting, enter formula as =A1="100" then select highlight color yellow.
The format says "Applies to =$B$1:$B$5", but when the value of A1 is 100 it
only highlights ONE CELL, not all of the celss that it is supposed to apply
to. It seems to highlight only the first cell and not the rest.

I have opened a new file and done just this one task and it continues to do
the same thing.

am I missing something stupid? I've been up for 20 hours.

Thanks

 

[Max]

If the value of cell A1 is 100, then highlight cells B1:B5 with yellow

Select B1:B5, then apply CF using Formula Is:
=$A$1=100
Format to taste > Ok out
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik
---

 

[David Biddulph]

B1:B5 is part of a column, not a row
To get more than one cell to be affected by one condition in CF, you ned to
make sure that you've got an appropriate combination of absolute and
relative addressing, hence try =A$1="100"
Are sure that you are looking for a text string of 100? If you are looking
for a number, get rid of the quote marks.

 

[Rod]

Yes, I meant B1:h1 was to be highlighted, but at any rate the problem is the
same. I select a range of cells (be it a row or a column) and then
conditionally format them to be highlighted based upon the value of another
cell, in this case if A1=100. I go through the routine steps of selecting the
desired cells, got t CF, select formula, add =A1=100, then select the desired
formatting.

When I plug 100 into cell A1, only the first cell of the range of cells to
format changes to the desired highlighting. I have retried this with
different cells using new spreadsheets with the same results. When I go back
and look at the formula, it has the correct range of sells indicated. They
simply don't highlight.

 

[Gord Dibben]

Select B1:H1

CF>Formula is: =$A1=100

Note the $ sign to fix column A as absolute.


Gord Dibben MS Excel MVP

 

[Rod]

AH HA! that did the trick. I don't quite get why the formula needs to be an
absolute. Does the formula essentially "copy" to all the cells in the
"applies to" range?

 

[Gord Dibben]

That is correct.

The formula is applied to all selected cells and if column is not absolute
the A1 would change to B1, C1, D1 etc.

You want to use only $Ax as the trigger cell for that row so you must lock
in the column A

This allows you to copy the CF down so each row will be painted.

The $A1 will change to $A2, $A3 etc.

See more in help on absolute and relative cell referencing.


Gord

 

[Rod]

Thanks, so much. That is exactly what I was doing wrong.

Gord, have you ever been going crazy looking at something and then you just
keep getting more frustrated because the clock is ticking away and you just
can't make it work. That's what was happening to me with this problem today.
Your input has literally made my day. I can actually go spend some time with
the family now!!! Thank you very much.

 

[David Biddulph]

You haven't addressed the point I raised regarding relative or absolute
addressing. If you stick to relative addressing it won't work. You need to
make the row or column reference absolute, as appropriate.