Free Microsoft Excel 2013 Quick Reference

Conditional Formatting a whole row based on column's values

when doing conditional formating is it possible to highlight the whole row based on a value, rather than just highlighting the cell?

Example: rows represent veterinarian's records, every time "dog" appears in column C, the row/record should be highlighted red, every time there is "cat" in column C, the row/record will be highlighted orange. The preference is to use conditional formating, since Column C is also conditional and has a formula inside.


Hi,

I would like to colour a whole row based on the value in the L column for that row.

Basically, if L3 (for example) = "Poss duplicate" I want that whole row to turn red.

I've been trying to use conditional formatting to do it but I can't seem to get it to colour the whole row.

Any help?

Thanks.

Hi All.

Re: Conditioning A Whole Row, Based On The Content Of A Single Cell.

I'm a little new to Excel, so apologies if this should be posted in a more Newbie orientated forum.

I have a basic spreadsheet. 40 rows long and 8 columns wide. Column 8 (the last column) contains only a single word. Depending on what that word is, I want that entire row (columns 1 - 8) to turn into a grey colour. So far, I can only figure out how to get an invidual cell to change colour.

Kind Regards
Emmily

Dear Friends,

I am to apply conditional formatting coloring entire row based on the details as follows:

COL C	COL E	            COL H	   COL W (DAYS)
5955	Stationery 	10/05/2009	55
5824	Stationery 	27/11/2009	
5801	Stationery 	15/09/2009	
5955	Appliances 	04/05/2008	
5801	Appliances 	27/12/2009	
5824	Stationery 	08/02/2009
In the above example, I just need to color entire row based on value mentioned in Col W -(minus) Col H dates. i.e. I just want to be reminded that From today, what are the dates (in col H) falling adding the no. of days in Col W (55) (In the above example it is in Bold). If some dates are falling, then the entire row should be filled with Red.

Thanking you,

Yours, acsishere

Ladies and Gentlemen,

I have a bit of a problem with conditional formatting. I've looked around for quite a bit and haven't found a solution.

I would like to highlight entire rows based on the values in column C. This is fairly straight forward for greater than, less than, or equal functions. How would I go about achieving this for a between function?

08:00:00 > x < 16:00:00

I am evaluating a time sheet which is linked to our electronic entry system and need to highlight employees entering after the beginning of the work day or leaving before the end of the work day.

Regards,

Steven

I have an interesting problem. I have the data in the following
format:

a1|b1|c1|d1|__|__|
a1|b1|__|__|e1|f1|
a2|b2|c2|d2|__|__|
a2|b2|__|__|e2|f2|

and I'd like to be able to merge the rows based on the values in the
first two columns. So the above table would become:

a1|b1|c1|d1|e1|f1|
a2|b2|c2|d2|e2|f2|

I have a large file in this format, where I must merge 2,3 or
sometimes 4 rows in this fashion.

Anyone ?

is it possible to use conditional formating based on the value in other cell

that is can I use conditional formating in cell A3 based on the value in
cell A1
--
Iceland is almost on the top of the world

Hi all,

My Excel Knowledge is quite limited to be honest and i have been searching for a solution to this problem but have not been able to come up with an answer.

I would like to conditionaly format a whole row depending on the value of one particular cell in that row.

In Simple terms, if Cell A1 had a value between 0 & 30 the row colour would be orange. if Cell A1 was bgetween 31 & 60 then the row colour turn to red.

Many thanks for your help in advance!

Kind Regards

Scoop

is it possible to use conditional formating based on the value in other cell

that is can I use conditional formating in cell A3 based on the value in
cell A1
--
Iceland is almost on the top of the world

I've tried, but I can't figure it out. How to use conditional formatting to
alternatively color rows based on values in a column?

For example, in B13:B3000, I have values like this:

Col B Row color I'd lilke
Apples Green
Apples Green
Pears White
Pears White
Pears White
Pears White
Bananas Green
Monkeys White
Monkeys White
Giraffes Green etc...

Thanks for your help...

I've tried, but I can't figure it out. How to use conditional formatting to
alternatively color rows based on values in a column?

For example, in B13:B3000, I have values like this:

Col B Row color I'd lilke
Apples Green
Apples Green
Pears White
Pears White
Pears White
Pears White
Bananas Green
Monkeys White
Monkeys White
Giraffes Green etc...

Thanks for your help...

Hi all,

I just wanted to know if it was possible to conditional format and entire row based on 1 of it's cells. e.g. I have a table with 3 columns (total, positive, negative). I want to format the rows of this column based on the overall percentages in the Total Column (i.e. if the total column is greater than 5% of the overall Total then the row should have no fill). So row A has a total at 10, positive 4, negative 6. Row A should then all be filled.

Hope that a) makes sense and b) can be done. If it is posible how do I do it??

Thanks

Hi All.

I have a spreadsheet that I use Conditional Formatting to colour a cell based on how many days till due date, This works fine, my question is I would like to have a Button on the spreadsheet that I can press and it will filter all that are of a certain colour

Example:
Button Saying - 7 or less Days till Due (Green)
Button Saying - 5 or less Days till Due (Amber)
Button Saying - Due/Over Due (Red) By pressing each button it will filter any row that has a cell formatted to the particular colour.

Can anone tell if and how this can be done. I know it is a big ask but please treat me as a newbie at all this.

Shazz
xx

I want to format a row based on the value of another cell. eg If cell N10 >
25 then fill (hi-light) row 10 (or A10:N10) with red colour.

is it possible to change the color of the whole row, hide the row, or apply 'strikeout' to the whole row (row a to row m) based on the value of column F?

I'm trying to format a whole row based on the contents of one cell.

Like this...

If D6 or D7, 9, 12, 30, etc. are filled with the letter "R", then I want
that whole row (i.e. 6, 7, 9, 12, 30) to be shaded red.

How can I do this using conditional formatting. I can get the particular
cell to shade red, but not the whole row...based on that one cell.

Thanks!

I am working on a project that requires me to match up rows based on matching values in 2 columns. In the attached file, Columns A and L have record numbers and all of those in L show up in A but not all in A show up in L. The data is arranged so columns A through K need to stay together and L through X need to stay together.

The Problem:

I need to align columns A through K and L through X by row based on matching values in column A and L. I don't have to keep the data in this format so long as rows with the same number in A and L line up.

Maybe there is another thread on this but I have searched everywhere over a couple days now and havent found a thing.

Thanks in advance

I want to run a macro that will protect all the cells of a range within a row based on the value of two cells within that same row.

This only needs to run once so a worksheet change is not necessary but it needs to be a small code instead of a conditional format because it needs to run after another macro runs. If i make it conditional, it will prevent my code from running.

I need to watch C1 and H1 to determine if R1:AI1 need to be locked or not.

If C1 = "Apple" then R1:AI1 = locked
OR
If H1 = "Pear" then R1:AI1 = locked

This has to be done on multiple rows (137 to be exact). Is there any way to do this using VBA?

Using the oldest version of Excel (200) I have to insure all members of our squad can utilize this function. Trying to format a whole row based on one cell in that row that reports number of days till the next physical. My formatting works correctly for the initial cell, but does not extend correctly to the rest of the row. I am using the "Cell value is" option. I used:

Cell value is between 1 and 365 - color green
Cell value is between 0 and -30 - Color Yellow
Cell value is between -31 and -365 Color Red.

All cells with no value in it are yellow, even if the original formatted cell is another color and the cells that have other data in them such as the physical date, have no color in them regardless. read and tried several approaches from other threads with no good results. considering the results from those threads, I am curious if it has to do with it being an older version.

Thanks in advance.

Could any one help me please!!!!!!
How can I select a whole row based on the text value of a cell.

Thanks

Frank

Hello ozgrid faithful,

I have been researching a VBA solution to a common problem, moving row(s) to a new sheet based on column criteria. I have found several responses on this forum that deal with this issue, however the VBA responses are too customized to the individual poster, and the number of unique criteria is too large to use an advanced filter (repeatedly). I'm hoping someone could provide generic VBA coding to the problem, which I have illustrated below:

A data table has 'x' number of columns and 'y' number of rows. Column A has attribute data that will determine which row(s) are copied to a new (VBA created) sheet, named after the attribute. The workbook will start with 1 sheet, and end with multiple (creating a new one each time the Macro is run).

I understand that a Pivot Table would similarly summarize this information, however I need to be able to electronically distribute a confidential list specific to each attribute (AA can't see AB's, AB can't see AA's, etc). I'm not sure how the code is setup, but please ensure that it is generic enough where the number of rows and columns can fluctuate (though column A will always contain the determining attribute).

I have attached a sample document with the criteria described above. Your help is greatly appreciated.

The solution will have 5 tabs ["Sheet1", "AA", "AB", "AC", "AD"], each with 7 columns. "AA" and "AB" will have 2 rows, "AC" and "AD" will have 1 row.

Greetings All,

I've got a simple spreadsheet that I'm essentially trying to show or hide rows based on a value in a column. Simple AutoFiltering won't work here though. There are 3 different values in column A, but I might want to see just the rows showing Value A, Value A&B Value A&C or all.

Any help you can lend would be greatly appreciated.

~Steve

I'd like to hide rows based on the value of a specific cell (that specific cell will always be C14, and it will have up to 8 variables selectable from a drop down list). For example, if C14 = Innovative/exploratory, i'd like to hide rows 15-45. If C14 = Technical Service, i'd like to hide rows 15-32, and 42-44. I'm not familiar with VB, but am a quick study - if someone's able to help me design these two, it's my hope that i can use that as a template to set the other conditions.

Thanks in advance!

Hi,

I have read several related threads and tried to adapt their answers to my application with no success. I am trying to write a macro that will hide rows based on that row's value in a certain column. Specifically, column AB contains sums and if a sum equals 0 I want to hide that row. The sums start at AB5 and go to AB118 but there are 4 gaps in the column at rows 25, 47, 70, and 94.

Thanks for any help you can give.

I have about 10,000 rows. I want to use a function or macro to delete
rows based on column criteria quickly.

For example, if Columns F-P are blank, I want to delete that row from
the worksheet.

I'm not sure the best way to go about this. I was thinking a macro,
but it always seems that there is function I've never heard of that
works much better. Thank you in advance for your help