Excel row formatting based on a cell value
Posted by Raj nair on August 13, 2001 8:19 AM
I am trying to format a row in excel to one color based on one cell value ( ie if cell value less than 1, color the whole row as brown ) is this possible - how can i achieve it ?
regards, raj
Posted by Mark W. on August 13, 2001 8:44 AM
Use conditional formatting. See the Help topic
for "Highlight data that meets conditions you
specify".
Posted by Eric on August 13, 2001 9:30 AM
?
Mark,
How do you get the conditional formatting function to format an entire row based on one cell in that row meeting a criterion? For instance if Col (A) had numbers random numbers from 1 to 100, and you wanted to highlight all of the rows in which the number in column A was greater than 50, could you use conditional formatting for that?
The only thing I could think of was to sort on that column, format the group, and then sort back to original order.
Thanks
Posted by Mark W. on August 13, 2001 10:07 AM
Re: ?
Let's say that cells A1:A4 contains...
{"cat";"dog";"cheese";"bird"} and you want to
apply a yellow pattern to the row containing
"cheese" in column A. Do the following...
1. Select row 1:4 leaving A1 as the active cell
2. Choose the Format | Conditional Formatting...
menu command
3. Specify Condition 1 as "Formula Is" using
the expression, =$A1="Cheese", and a yellow pattern
That should do it!!!
Posted by Aladin Akyurek on August 13, 2001 10:33 AM
Re: ?
With numeric values, you need to change the formula to:
=$A1+0<1 [ to take up Raj's criterion ]
Aladin
Posted by Eric on August 13, 2001 11:18 AM
Thanks for taking the time to explain that to me, it will be a huge help (NT)!
Posted by Mark W. on August 13, 2001 12:44 PM
That's odd...
I can't see Raj's reply to my posting!!! Evidently,
you can see it, but I can't. I guess this board
is still acting flaky!
But, that aside, why do you believe that $A1
should be coerced? With numeric values, you need to change the formula to: =$A1+0<1 [ to take up Raj's criterion ] Aladin
Posted by Aladin Akyurek on August 13, 2001 1:10 PM
Re: That's odd...
I don't think there is a reply from Raj.
Couldn't get it work first. Now, I got it where it should be without coercion. Seem to run up against something else: I do the formatting just for A1:A4 (with num values). If I enter some value, (say, in A6), that happens to meet the crit used in A1:A4, A6 (not row 6) gets the formatting of A1:A4. That's odd too. It seems applying cond formatting to entire rows is somewhat flaky.
Posted by Mark W. on August 13, 2001 2:07 PM
Oh! That was...
...that was Eric's posting. I'm afraid I played
billiards too late last night (well past 1AM) and
my brain isn't firing on all cylinders. : )
> It seems applying cond formatting to entire
> rows is somewhat flaky
I believe that you must use a reference to
entire rows such as 1:4 rather than a cell range.
Posted by Raj Nair on August 14, 2001 10:05 AM
Re: Oh! That was...
Thanks for the comments. I tried row formatting based on coditions - but it started giving me weird problems when i tried to copy the rules through mutliple rows. I wrote a format macro instead - works predictably.
thanks for all the tips. it helped.
Posted by Mark W. on August 14, 2001 2:52 PM
Re: Oh! That was...
What sort of "weird problems"? What type of
condition did you use? What was the conditional
formula? : ...that was Eric's posting. I'm afraid I played
Posted by Ian on August 14, 2001 3:59 PM
Re: Oh! That was...
Raj
you can highlight the whole area (not just 1 row) and place the formula in the conditional Formatting Box.
For ease: take note to highlight top left to bottem right, this way just check the Name Box of the cell to make sure you've got the right start point for the formula.
do Formula Is _ =$A1<1 and condition pattern
assuming the value you you want to check against is in column A:
this WILL work
Ian
