Back to Forms in Excel VBA archive index

Back to archive home

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

Use conditional formatting. See the Help topic

for "Highlight data that meets conditions you

specify".

for "Highlight data that meets conditions you

specify".

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

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

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...

{"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!!!

With numeric values, you need to change the formula to:

=$A1+0<1 [ to take up Raj's criterion ]

Aladin

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!

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

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.

...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. : )

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.

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.

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

condition did you use? What was the conditional

formula? : ...that was Eric's posting. I'm afraid I played

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

This archive is from the original message board at www.MrExcel.com.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.