Email:      Pass:    Pass?


Advertisements


Free Excel Forum

Condition Format-entire Line

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hello
I am using Excel 2007 and having some trouble with conditional formatting an entire line.

What I currently have is a formula in Column J: =IF(K9="Match","Match", " "). What I would like to do is if "Match" appears , highlight the entire row ( J : A ).
hope this makes sense.
Thanks for your help


Similar Excel Video Tutorials

Helpful Excel Macros

Vlookup Function That Searches The Entire Workbook - UDF
- This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Highlight Cells which Contain Formulas
- This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight
Highlight Cells with Text or Formulas (non-empty cells)
- This macro will highlight all cells in excel which are not empty. This means that if a cell contains formulas, text, nu
Filter Data to Show the Top 10 Percent of the Data Set in Excel - AutoFilter
- This Excel macro filters a set of data in Excel to display only the top 10 percent of that data set. This means that th

Similar Topics







Hello
Any advice is appreciated.
Strating in Column C2, I have rows of dates beginning from: Oct-11-10 (Mon) to Dec-31-11 (Sat). In column P2 I Have listed all the statuatory holidays, such as Oct-11-10 (Mon) and Dec-25-10 (Sat) and so on...
What I am trying to do is implement a conditional format in Column C that will get highlighted if there is a data match in ColumnP. I would like to highlight the entire row Column C to Column J.
Hope this makes sense, Thanks for your help.


I am trying to set up the conditional formatting in a spread sheet to highlight / format an entire row depending on the information found in cell. EX: highlight the entire Row B once shade if cell B10 = Accepted and another shade if B10 = Rejected. I am only acquainted in the most basic sense with conditional formatting and formulas and even less so in Excel 07 and 10.

Hi guys,

I have been messing with this for about an hour. I have a sheet with 400 rows, I need conditional formatting to highlight the entire row when the value column AK and AF do not match.

Can this be done with conditional formatting and if not VBA?




Back again since i got good help yesterday...

what im looking to do now is color fill a cell based on the value of an entire row.

What i have is a sheet that shows our current orders. The first column has the order number, which covers two rows. From there, the first row for that order is "Quantity Ordered" and the second row is "Quantity Received". From there, it is about 200 columns long.

What i am looking to do, is have three variables to determine the color of the cell with the order number in it.

1. One color to show the order has not come in yet (basically the received line is empty and does not match the ordered line).
2. One color to show the order is partial (this would be SOME values in the received line match values on the ordered line).
3. One color to show received (this would be when all the values of the received line, match all the values on the ordered line).

thanks again!

P.S. using excel 2007

I need to find a way to highlight any cells in A:C that is greater than the value in column E of the same row.

Each row needs to be evaulated using the same criteria, I can make the formatting work by manually inserting a formula for each row but I'm hoping there is a way to set up the entire sheet instead of doing it line by line.

I am using Excel 2007

Thanks in advance for any help you can provide.


I was wondering if anyone can help me with this problem

I would like to highlight an entire Row if the number is >17

I have tried to use conditional formatting; and i'm hoping its something rather simple that i'm over looking

I'm trying to get the anything in column J >17 to high light the entire row

Is there a simple way of doing this or does conditional formatting allow this or would it simply highlight that information that is >17?

If someone can point me in the right direction that would be gretly appriciated


I asked this questions yesterday but left out the i need to do this for the entire column A if Column L < 2. When I used =L2< 2 for the condition it only gives me what I need in A2. I need this condition for each cell in A to match the same cell in L. EX. A2 Highlighted when L2

Hi,

I have run into a problem with conditional formatting which I am fairly new to. I am trying to apply a conditional format (color code) to a cell to match the conditional formatting of another cell which is linked to a lookup table.

So, for example my first conditional format (in Excel 2007) is Cell Value = "Rates", Format = Orange, Applies to entire worksheet (or =$1:$65536). So anything on the worksheet that has the word "Rates" in it is colored orange.

Second, I have a lookup table where a name is associated with the word "Rates". So C2 Product Category says "Rates" and D2 has a name (Joe Smith) and so forth. I have figured out how to have Joe Smith (B2) highlighted orange as well based on conditional format of "=$C3="Rates"" applied to the entire lookup table. I have other 'Product Categories' besides Rates and different names, so the names need to be matching the color associated with the product category. However, I would like to do the same thing for another worksheet that has the name "Joe Smith". So if Joe Smith appears somewhere else, it would appear to be orange solely because "Rates" is orange.

Hopefully this makes sense. How can I accomplish this?

Thanks,

Kelly

A B C D Worksheet 1 Seat Num ID Product Name R31 03AZ005 Rates Steve Bentley R29 03AZ008 Rates Mike Bosi S25 03BF014 X Rates Sales OPEN S34 03BJ005 RMBS Michael Zigrossi S32 03BJ008 RMBS Alvin Sarabanchong Worksheet 2 Rates Q: How do I make these names match the corresponding Product color? RMBS --------> Mike Bosi Michael Zigrossi

Hello,

I have a range of data that I would like to conditionally format all cells that have an error to have white font. Unfortunately, I cannot use a function like this:

if(iserror(function,"",function))

because the formulas in this range are already too long to be able to duplicate for the 'value if false' argument. I have also tried using the conditional formatting tool by setting "Formula Is" equal to:
=ISERROR($A$1)
but this only whites out errors in cell A1 or column A (depending on what I selected.

I have pasted the formula below that is giving me the error if anyone has any ideas on how to modify the formula as well. It is basically an index match formula that is looking at several different data points on another worksheet and averaging them. Thanks in advance.

=AVERAGE(INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$2,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH( $C18,SRD!$A$4:$A$351,0),MATCH(M$3,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$4,SRD! $D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$5,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351, MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$6,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$ 7,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$13,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$ DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$8,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),M ATCH(M$9,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$10,SRD!$D$2:$DG$2,0)),INDEX(SRD !$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$11,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$ 351,0),MATCH(M$12,SRD!$D$2:$DG$2,0)))


In Excel 2007

I am trying to apply conditional formatting to an entire row based on ranges of test scores
Everything works fine highlighting a cell and using Highlight cell rules with the Between feature but I need the entire row to be highlighted if it meets the condition

90-100% = green
80-89% = yellow
65-79% = white
0-64% = red

Therefore I thought formula conditional rules would work instead
I have tried IF AND and combinations to get all the colors to show accross the appropriate rows

I seem to be forced to write formulas with each invidivual > or < condition such as
(TEST Scores start in Y2)
$Y2>89%
Range of cells to highlight row = =$A$2:$Y$14

I get close but the last conditional seem to wipe out all my other coloring - regardless of the order of the conditions
this all seems crazy just to get an entire row to display the highlighting
I don't think this is an uncommon request I have seen solutions all over the web but not applying a series of different colors based on value ranges

Anyone have a solution that is not VBA since this is for my user community who do not do VBA

Many thanks
Leslie Yambor


I have a long spreadsheet with info through column AT and Rows through 22. In Column S I want to input Conditional Formatting that will highlight the entire row of cells with yellow fill if my drop down menu in Column S is "Yes". So far I have only been able to formulate the Conditional Formatting to highlight the particular cell in Column S when "Yes" is displayed, not the entire Row. Does anyone know how to Conditionally Format an entire Row to be highlighted if a cell in that row displays certain info? Any responses are greatly appreciated! Thanks.


I'm having some trouble with my conditional formatting. I have a table, formatted using the table style, and would like to apply conditional formatting to an entire row of the table based on the value of the last column. I can get the conditional formatting to work on 1 row, but would like to have it automatically apply the formatting when a new row is added to the table. Any suggestions?

Note: the last column in the table is a hidden column that just has a boolean value of True or False. For False, the entire row should highlight yellow.


Hello all,

The subject line pretty much describes what I want to do. Ive read a ton online and for whatever reason I cant seem to get it to work. No problems getting it to highlight a single cell but I would like to get the entire row. Thanks in advance for any help you can offer. Attached is what I have so far.

For clarification for those who cant download the example right now, the cell with the date currently is set to color itself red if the date is within 30 days from now, and yellow if its between 30 and 60. I would like the entire row to highlight instead of just that cell.


hi guys,

I am struggeling with this which I hope you can help...




I have a work sheet called 'Cross Ref data' which I want to put a keyword in column A (row 1,2,3,4,5,6,7,8 etc)

I then want to be able to run a macro that will grab the contents of A1 in WS called 'Cross Ref data' and check if it appears in column D and E in WS 'Results'


If there is a match then for the entire line within WS 'Results' be copied to a WS that already exists called "Cross Ref Results" (please see the below heading '88888***********8888' for the layout)


If a match appears in both column D and E in the WS 'Results' then only for 1 of the line items to be copied not both (... i do have remove duplicate macro though that does this but it is a bit dirty).



88888***********8888

Also, this will make it the best..

If a match is found for there to be a heading in the "Cross Ref Results" work sheet titled "Found in both" and for the match to upper below.

If there is not a match for there to be a heading "only appears in your data"

If for example 10 names are entered in the 'Cross Ref data' WS though there 12 names in the 'Results' WS in column D then the 2 line items can appear under a heading "only appears in XXX"

The 3 above said heading can be pre populated in the 'Cross Ref data' WS if that makes it easier...

Thanks soooooo much guys, If this works then i will be over the moon, i hope it can be done.

thanks


I am trying to Highlight an entire row, if a vlue exists within a column. I have read many forums in which you can create a macro to do this. I have "10" values that I would like to use as far as values within a column. I would like to run the macro, and if a number exists, I would like it to highlight the entire row.
The cells that i would like to have read are within Columns "w" and "x". The cell values are within a merged cell because of the way that the program exports the report into excel.

The values that I would like to have met are :

96- Highlight Entire Row Orange
237-Highlight Entire Row Yellow
402-Highlight Entire Row Blue
680-Highlight Entire Row Blue
871-Highlight Entire Row Blue
827-Highlight Entire Row Blue
213-Highlight Entire Row Blue
833-Highlight Entire Row Blue
339-Highlight Entire Row Blue

Please let me know If I can post the excel spreadsheet, and whether or not I can be helped. Thanks !


I have a spreadsheet with 18 columns of data, and I need to look for variances.

I need to make the entire row highlight, if one value in the row does not equal all the rest. If I can use conditional formatting, then I would like to add a second parameter that highlights the particular cell that is off.

I know that you can conditional format any unique values, but if it isn't harder I would prefer to highlight the entire row.

Another step would be that, sometimes the values change, so perhaps the first 9 columns are 7.00 and the next 9 are 8.00, I would like it to highlight that change.

Any way to do this?


Looks like the upgraded conditional formatting in Excel 2007 behaves very differently from 2003.

I'm writing a macro that uses conditional formatting to change the entire row color if the date in column C is before today. I have to support both Excel 2003 & 2007.

In Excel 2003, select the entire list range and assign this formatting formula. 34 is the last row of data
Code:

=$C34

Looks like the upgraded conditional formatting in Excel 2007 behaves very differently from 2003.

I'm writing a macro that uses conditional formatting to change the entire row color if the date in column C is before today. I have to support both Excel 2003 & 2007.

In Excel 2003, select the entire list range and assign this formatting formula. 34 is the last row of data
Code:

=$C34

Hi,

I would like if possible that if 2 numbers were to match within a column then the entire row in which they are would highlight in a colour. Can this be done??

Cheers

Chris




I have been working in and created a spreadsheet with tons of macro code to help create command scripts that I use to configure routers.
During my data research, I will locate a block of text within a large text file and copy that to a cell within my sheet. The general layout is each row contains all data related to a specific customer. Each column will contain specific types of data for all customers. What I need to be able to do is take one cell of multi-line text and compare it to the original text file (which might contain updates that I need to catch). So, I need to search within a large text file to match several lines of text from one cell, where one or more lines of text in that cell may not match the entire relative area in the larger text file. It may be just enough to find/match the entire cell with the complete text data. If I can't match the entire cell, I'll replace it completely with what I will have to manually find anyway. I hope I asked this properly.

I am using Excel 2007 and would like to be able to format an entire column based on the date at the top. I have a spreadsheet with each weekday at the top. Each date cell is actually 3 cells merged together so there are 3 columns below it. I would like all 3 of those columns to be highlighted on the column for the current day. I used conditional formatting to format the cell at the top that contains the current day, but I haven't been able to figure out how to highlight the entire colunn. Thanks for any help or direction you can provide.


I'm trying to figure out the best way to go about this.

I have a sreadsheet with subtotals and above each subtotaled line are the rows that need to be formatted if they meet the proper criteria. I'm highlighting the rows whose cells in columns 'G' and 'I' (relative reference)perform lower than the % variance of the subtotals (absolute reference) hence the criteria changes for each subtotal.

I'm thinking of a nested If-then command to highlight an entire row that meets a certain criteria but don't really know the syntax of how to do so. Specifically using a loop structure to look at the contents of specific cells and if it matches then to highlight the entire row if not then onto the next and so on so forth until all that match are formatted. But this will have to be done for only a specific range, not the whole worksheet. For each section of the worksheet I will have to alter the code around to reflect the new % variances and change the selection accordingly.

Hope I made sense.

Can anyone just provide a general format for this? Thanks a bunch.


I've run into a problem with VBA in Excel 2007 while working with conditional formatting. I need to apply a conditional formatting to a data range. However, the subroutine may be run again on the same set of data and I don't want to reapply the conditional formatting to the same data range if it already has the formatting applied. One approach would be simply to delete all conditional formatting on the entire data range, but there could be additional conditional formats which should remain in place. So I'm trying to find a way to cycle through all conditional formatting conditions to see if any of them match the one I'm wanting to apply. If any match what I'm wanting to apply, I'll just skip adding it again.

But I can't seem to find a way in vba to get the formula back out of excel.

Using:
Code:

Workbooks("testbook.xlsm").Sheets("sheet1").ListObjects("DataTable") _
  .DataBodyRange.FormatConditions(index).Interior.ColorIndex


I can have the fill color returned for a given format condition. But is there any way to return the formula in the format condition? I've tried:

Code:

Workbooks("testbook.xlsm").Sheets("sheet1").ListObjects("DataTable") _
  .DataBodyRange.FormatConditions(index).Formula1


but this seems to be only able to set a value, not return one.

Any ideas?

Thanks,

Will




Hey guys,

I'm trying to create a calendar of sorts in excel for scheduling 'deals' to send out. I'm having trouble creating a conditional formatting function that can look horizontally to see if another cell contains the same content, if it does, highlight that original cell. Now the problem is the "time frame"/(cells) to check needs to be dynamic so I can use this one condition across the entire row but only looking back 60 days. I'm using a match function to check if it's true.

Attached is an example worksheet I'm working with, with conditional formatting already in place in cell BV22. Any help would be much appreciated.

KCCO - It's Friday at least.

Hello,

I'm new to conditional formatting and I want to conditionally format an entire row based on a single value, i.e. the values based in column B below. However, I want each rating to have its own colour, for instance, a 10 Rating would highlight the entire row Green, a rating of 2 would highlight the entire row red, a rating of 5 would highlight the row yellow etc..

Product (Column A)
a
b
c
d
e
f

Rating (Column B)
10
8
7
6
5
2

Description (Column C)
Excellent
Very Good
Good
Good
Average
Poor

Any help on this will be much appreciated. Thanks