Formatting Rows By Condition

P

Paras Shah

I was trying to extend the Conditional Formatting option so that based on
the condition I specify it changes the formatting for the entire row that
the cell is in, NOT just the single cell.

Can anyone help with this?

Simple Example.
For any cell where the value = n make the font color in that entire row
green.

Thanks in advance.

-Paras
 
M

mrice

I would suggest a macro linked to the sheet_change event which checked
every cell in the row where the target was and formatted appropriately.
 
B

Bryan Hessey

Hi,

Although you said 'entire row' - if you are meaning just the first few
cells, then, on Row 10, for the format of columns A to J, based on any
entry in columns A to F, select A10 to J10, Format, Conditional
Formatting, Formula is, =$A10&$B10&$C10&$D10&$E10&$F10<>""
and set the format you require.

Where any value occurs in A10:F10 then A10 to J10 will reflect the
chosen format.

HTH

--
 
A

Alex

Conditional formatting can be applied just like any other format. Get
the conditional format for one column to work the way you want and then
apply the format for that column to all the others you want.

The easy way to do this is to select the column that you want to use as
the template and copy it to the clipboard. Select all the other
columns and select edit->paste special->formats only and you should be
good to go.

In the formula for your CF, be sure to watch the dolar signs! The
formula there works just like a formula that you are autofilling, so
lock your rows and colums accordingly.

Hope this helps!

Alex.
 
P

Paras Shah

Hi Alex-

Thanks for your note. Unfortunately it does not help that much. What I want
is conceptually simple.

Right now, I have one condition in the conditional formatting window; "If
cell value is equal to 'y' then change the font to green color."
As soon as I put a "y" in a cell it is green colored font. As soon as that
same cell changes to another value it automatically goes back to the default
color.

What I want is that as soon as a "y" is in a cell, then the font for that
entire row becomes green. I want it to be automatic, just like how it works
for the individual cell.

-Paras
 
A

Alex

In your conditional format, change it to look at a formula instead of a
condition.

If the cell you want to look at is A1, then make your formula be:

=if($A$1="y",true,false)

When you are done and the individual cell works the way you want it to,
copy the cell to the clipboard. Select the row and do edit->paste
special->formats only,

This will do exactly what you ask!

Alex.
 
P

Paras Shah

I see what you are saying. Unfortunately, some of the other columns have
date and number formats and while this works to change the color, it also
changes my date formats back to text.

-Paras
 
P

Paras Shah

Actually, when I tried this it copied the bold and text formatting to the
entire row, but it did not copy the font color. So, it still messes up my
date formatting and it does not even copy over the color.

-Paras
 
P

Paras Shah

Ok. This may have not been the most elegant solution, but it works:

I am using this formula for each row I want the conditional formatting for:
=IF($E$7="y",TRUE,FALSE)

For each row, I change the row number in the formula respectively. There
were only 7 rows I needed this on, so I selected an entire row, went to the
conditional formatting menu and copied the formula always changing the row
number.

-Paras
 
A

Alex

That is one way to do it. If you change $E$7 to $E7 then it should
auto-increment when you do a fill of the formatting.

Alex.
 
Top