Conditional row color based on a cell value

Jeremy (US) asked me a question about coloring based on a cells value:

First your product ASAP Utilities is great and has been a life saver. One feature that would be outstanding is a conditional row color based on a cell value.
For instance there are numerous times I have to color a row because the value of a cell in a column is set either TRUE of FALSE or 0 or 1 and I currently do this by hand review.
If the ASAP utilities has a way to do this already I would love to know how.

Excel has a great built-in function to achieve this: Conditional Formatting (In the menu Format » Conditional formatting). The first time it might be a little difficult to find your way to do this but if you spend a little time with it you will soon realize it is powerful function.

In the following example if we want the rows to be colored blue when column C is true and orange if the value is 1, we can use the following formula's:
Select all the cells that need to be colored. Usually we color a cell based an its value and use the [Cell Value Is ] in the conditional formatting box. To format cells based on other cells you need the [Cell Formula Is].
We use the =INDIRECT() function to get the value of column C for each row:
If the value is TRUE : =INDIRECT("C"&ROW())=TRUE
If the value is 1 : =INDIRECT("C"&ROW())=1

The result:

Download the example workbook

Interesting links:
http://www.cpearson.com/excel/cformatting.htm
http://www.mrexcel.com/tip054.shtml
http://www.contextures.com/xlCondFormat01.html

A "quick and dirty" method is using:
ASAP Utilities » Select » Conditional row and column select, hide or delete
and then color the selected rows.

109 Responses to “Conditional row color based on a cell value”

  1. Had to wrap my value in quotes to achieve this: =INDIRECT("A"&ROW())="Old" . . . maybe because it was being interpreted as a string as opposed to a value?

  2. Thank You Very Much……..
    I am looking for this trick for the last six months or so
    thanks a lot

  3. in Excel 2007 I need to conditally format a row (fill with color) based on text rather than a numeric value. I have five worksheets, and in all of those worksheets I need the rows highlighted based on BrandOne, BrandTwo, BrandThree… arrrgh… I can't seem to get the formula right to get the entire row highlighted. When I say "BrandOne" it only highlights that cell and not the entire row… Any suggestions?

  4. Hello Willie,

    You have to use the conditional formatting for all cells in the entire row. Did you do that?

    Kind regards,
    Bastien Mensink

  5. Thanks a lot.
    That helped.

  6. This is wonderfull. I have been looking for something like this for a long time. Lot's of website that I used for reference didn't have any examples, but you did and it helped me see what I needed to do. Thanks for the great help.

  7. This is by far the Best code I've found. I knew it existed and I've been looking for it since yesterday. Works like a Charm…if you are using strings instead of numbers, just put them like that: "STRING" and it works.

    I would only like to find out one thing…the Exel Conditional Formating only lets you create 3 contidions! I am working with 5 possible values to change my row color. How can I do that?

  8. Hello Fred,

    Until Excel 2007 you only have three options for conditional formatting. In Excel 2007 this limit was removed and it is only limited by available memory.
    There are workarounds by using macros to set the conditional formatting, but that is more complex and the performance is slower than the built-in method.

    Kind regards,
    Bastien

  9. Hi Bastien,

    Thanks for your reply. Unfortunately we don’t have Excel 2007 here at work?. I do have it Home…but I don’t think it would work if I create 5 conditions on Excel 2007 and then export the file to Excel 2003.

    I am not too familiar with Macros either, and they have been disabled here for security reasons…Hmmm, I don’t know what to do then.

    So if beside the macro, there is no other way…I’m in trouble…lol!

  10. Hello Fred,

    Creating it in 2007 with 5 conditions wouldn't bring the 5 conditions to 2003.
    As an alternative you could use certain values instead of only colors by using the IF formula in a cell to display a certain value if a condition is met.

    You can also use a custom number format to specify a font color, for example
    [Red][< =10]#;[Green][>10]#;General
    Although this is not very good documented and it seems to be limited to two conditions.
    http://office.microsoft.com/en-us/excel/HP051995001033.aspx

    The following page is a valuable resource on conditional formatting:
    http://www.mvps.org/dmcritchie/excel/condfmt.htm

    If the numbers do not change a lot you can use ASAP Utilities to select certain values and then color them:
    ASAP Utilities » Select » Conditional select cells…
    http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=7&utilities=Select

    Kind regards,
    Bastien

  11. hi was wondering if someone could help me. i am using =INDIRECT("K"&ROW())="Converted" formula and work like a dream…

    however i now need to add extra part. i am trying to change the colour of the row if column "N" states its Invoiced.

    so column K is status. colun N is invoiced or not….

    i want row to be Green if status in colum K is "Converted" – got that far using the above BUT now i was row to Red if Colum K is "Converted" AND colun N is "invoiced"

    anyone help??? PLease

  12. Hello Rachel,

    If you want two conditions in your formula you can use the =AND() formula.
    Please have a look in Excel's help on this formula.

    Kind regards,
    Bastien

  13. The below formula is working fine but =INDIRECT("C"&ROW())=false is not working. Can any one help on this??

    =INDIRECT("C"&ROW())=TRUE

  14. Hello Maha,

    The formula should work. However the formula =INDIRECT("C"&ROW())=FALSE also returns TRUE if a cell is empty.

    Kind regards,
    Bastien

  15. Thanks, that works great!!!

  16. Thanks you so much. I went thru couple of web sites. your solution was so easy to follow.

  17. WHat if I want to color a back ground based on alphanumeric values? I need to change the color to red if the value has an "R" in it such as 15R or 1R and AMBER if the value has an "A" in it such as 1A or 18A

    please answer by email??

  18. Hello Tom,

    You can do this by using the FIND() functions.
    You can find an example of that on the following page:
    http://chandoo.org/wp/2009/03/31/search-with-conditional-formatting/

    Kind regards,
    Bastien

  19. Thank you! my brain was about to explode typing to solve that one!

    you have saved my sanity :-)

  20. How do you do the opposite, that is color the column based on a value in the first row. in your example I want the rows to be colored blue when Row 1 is 'Col D'

  21. Hello Bew,

    In that case you can use the following formula: =$D$1=TRUE

    Kind regards,
    Bastien

  22. thanks, maybe my question wasn't very clear..

    in the example table at the top
    I want a conditional formatting formula so that if in Row 2 (A2:F2 in this example) any value is "blah 71" then the whole column would be coloured (D2:D17 in this case)

    obviously in the table above there is only one occurance of "blah 71" in Row 2, but what if that value was present several times in a bigger table?

  23. Hello BEW,

    The condidtional formatting is set for each cell. So if you want the entire column to be colored you have to set the conditional formatting for all the cells in the column.
    If the value appears more than once it works the same as when it appears once, the formula result is TRUE if the values appears (once or more) in the given range.

    Kind regards,
    Bastien

  24. How To Highlight Rows Based on a Cell Value in Excel…

    Here’s how to highlight rows based on a cell value in Excel 2007 and Excel 2010.  Reference this…

  25. Hey man this is really helpfull, i have been searching for this for a long time. Thnaks a lot and God bless You!!!

  26. Quick question:

    If I want to take a list of two numbers, and if the difference between those numbers is greater than 5000 color it red, and if it is less than 5000 but greater than 2000 it is yellow and below that is not colored. How would I achieve this? I do not want to add any summary data in another column, is it possibe to use the function to calculate this for all items in a specific column?

    Darsen

  27. Hi

    Thanks for this, saved me a lot of time. However, I still have a small problem. Cell A1, A2, and A3 are empty until a currenly value is added. When a currency value is added, cell A4 sums A1, A2, and A3. I want to do the following:

    When cells A1, A2 and A3 have no value (empty), do nothing
    When the total of cells A1, A2 & A3 is zero – change the row colour to say, red
    When the total of cells A1, A2 & A3 is greater than zero, change the row colour to Green

    I can do the changing colours ok, but I don't know how to do nothing, because cell A4 is summing A1, A2 and A3 as zero even if they are empty and so changing the row colour to red, I don't want that I want the row coulour not to change to anything until there is a value entered, the value being zero or anything else.

    Basically I'm trying to show rows that are dead/lost and those that are still alive.

  28. Thanks for the great tip – if only the Microsoft Office Assistant 'Cat' was as helpful as you!!

  29. This is useful if you know the exact cell value to color like 1, TRUE etc, but how can you color a group of rows with same cell value?

    ex. 123
    123
    123
    245
    245
    385
    385
    385

  30. Awesome. Thank yuo so much. I have searched many sites for this simple solution and you were the first one that worked. Thanks again!

  31. How can I get this to work with multiple condition? I have been trying

    =INDIRECT(AND(("I"&ROW())>=1, ("B"&ROW())=0))

    and what is really really baffling me now is that if I have a new spreadsheet with only 2 columns and type 2 into B1 and then type =AND("B"&ROW()=2) into C1 it returns false no matter what the value of B1 it would seem…….. if I type ="B"&ROW() into D1 though it prints B1 in the cell so the reference seems to be working………… argghhhhhh!!

  32. Hello everybody,

    Unfortunately I don't have the time to answer all general (not related to ASAP Utilities) Excel questions.
    I use it to give some examples and share some knowledge and give you ideas on how you can approach certain things in Excel.
    If you want feedback on other than the given example, please seek for help in the Excel newsgroups or forums you find on the internet.
    The following page shows links to interesting resources and forums:
    http://www.asap-utilities.com/excel-tips-link-websites.php

    Kind regards,
    Bastien

  33. Hi,

    This is great, having been looking for something like this for ages. I need to expand the criteria, only slightly though.

    I basically need:

    If Cells = less than 4 then row is green
    If Cells = 4 then row is amber
    If Cells = greater than 5 then row is red

    I would also like the conditional cells to be the result of a sum

    =NETWORKDAYS(C2,E2)+1

    Can anyone help?

    Thanks

    ben

  34. This is a very fantastic tip, i am searching from so many days, can you please tell me if there is any idea on how we can add more conditions ( because i am using 2003).

  35. I am still not clear how to change a column base on the row… so i wand to highlight any column that the row 13 of it is zero after highliting my colomn A7 to A44, i opened the conditional formatting and i typed
    Formula is =indirect("13", column())=0
    i think it is totally wrong please help

  36. Hello,

    Is there a way to do color code rows per unique number on the colum? (want different colors per specific numbers in each row) ie. gray for 1234, white for 5432, gray for 8901 and so on… Please help….??????

    1234
    1234
    1234
    5432
    5432
    8901
    8901
    6789
    6789
    8764
    8764

  37. Great update! This saved a lot of my time !

  38. Thanks SO much. Saved my butt helping me with formatting to trap human error at work.
    You da man!

  39. Hi

    I'm using excel 2003. i also need this kind of a code. but its not working for me. why is that ?

    Thanks you

    Nazim

  40. wazzzup,

    can anyone tell me how to make row change color if i put date (12.12.2009) in cell???
    i tried this:

    =INDIRECT("C"&ROW())=DATE
    =INDIRECT("C"&ROW())=dd/mm/yyyy and similar,
    but nothing is working?

  41. If am working in Excel 2007. I am trying to do a conditional formatting in which if a cell =#N/A then to highlight the entire row. I am having a difficult time with this. I cannot use the "if(iserror)" as this does not work in 2003. I tried If(isna) and nothing. Any suggestions???

  42. Hello,
    I am trying to change some cells because of other cells. But using colour. I.e. If a certain cell has red writing, then another column of cells will colour red too. Please help! This is a GCSE project.

  43. Great Tip – Thank you for taking your time to help us!

  44. Thanks.. This was very helpful.
    JIC anyone was struggling like me..
    Conditional Formatting -> Manage Rules -> New Rule (or Edit Rule) -> Use a Formula to Determine… in this section type: =Indirect(""&ROW())="""
    You can select the part of worksheet you want to apply this to before or after.. if you have a big worksheet in the beginning select a few rows and test before applying to the entire worksheet.

  45. Awesome info, helped me greatly. Thanks!

  46. Hi

    can anyone help me with a conditional format problem in Excel 2007 – I want to format a column of values based on another column of values, one colour for under 30, one for 30 to 60 and one for over 60 – I can do the under 30 and over 60 no problem but I have no idea how to do the formula for less than 60 but greater than 30? any help would be appreciated

  47. I have been looking for this for hours. I needed to add a conditional format to a large column of cells to check the sum of their respective rows but couldn't figure out how to reference the cells without using the excel coordinates in the conditional formatting. I could have created a program in VB with a button to accomplish this, but I really wanted the color change to be automatic as soon as information is entered, not when the macro button is clicked. Thanks.

  48. Hello can anyone help me.

    If in a range (for example R5:T13) a cell is blank how can I get get range A5:T13 to turn blue??

    Much thanks

  49. Great info… I was looking for this for a long time. Thanks.

  50. Wow thanks so much… this really helped me.. :)

  51. great info, but I have something a little more complicated here…

    In Column A I have "Date Checked In" and in Column B I have "Time Checked In". In column C I have the following formula used to have a "Call Back By" within 2 hours:

    =DATE(YEAR(A3), MONTH(A3), DAY(A3))+TIME(HOUR(B3)+2, MINUTE(B3), SECOND(B3))

    I've been trying this for awhile.. How can I conditionally format the row to highlight if the date/time in Column C exceeds the current time (i.e. NOW())?

  52. Whoops, I meant "…if the current time exceeds the date/time in Column C"

  53. I figured it out, just FYI. There has to be a reference cell; as an example, put A1 as:
    =NOW()
    column C just needs to be conditionally formatted to be:
    >=$A$1

    The trick is to have both cells formatted the same way, i.e. mm/dd/yyyy [h]:mm pm

  54. Thank you so much, I have been looking for this for weeks!

  55. Please Help!

    I have been using a row interval color change macro for a few years. I believe that it is the best tool that could help me with the following… I just can't find how.

    I need a way for excel to do a color transition "count"….

    I need excel to now count the number of interval row color changes (transitions)… not a row count, but a row group count based on color (4 rows, then three rows, then one, then, one, etc – no pattern – I use a two color transition).

    I do this manually by numbering only the first row color change… there has to be a way to tell excel to do this, no?

    background:
    I use access and pull a table into excel which has a list of items applied for by address.

    If someone applies for 3 things on an application (they applied or requested something), it gets entered as 3 items creating 3 records and 3 rows on a table.

    Lets say that typically there is no pattern to the number of items being requested on a single application –

    To actually "see" how many items were entered from a single application, sort by record # (order entered)- highlight interval rows by address – this is how I can tell groups of rows were from one application, on the same day, from a single person.

    My goal is to count the number of "applications" despite the number of "items/records/rows".

    The data I'm working with goes back to '89 and these items have never been "linked" together as originating from a single application. The only way I can see their grouping is through the highlight row intervals as the data changes.

    OK – now I want to count the INTERVALS. I want a count of applications.

    My spreadsheet has 2 colors – blue and yellow. I might have 4 yellow rows, then 2 blue, then single row transitions for a while… etc.

    IS THERE A WAY FOR EXCEL TO COUNT THE "CHUNKS" OF COLORED ROWS (not a row count – but an interval or color transition count)???

  56. Works perfect, best answer on the web

  57. =INDIRECT("AM"&ROW())=R

    tHE FORMULA YOU TYPED CONTAINS AN ERROR

  58. Hello Nick,

    If it is your own formula to which you are referring, then you should put quotes around the letter R, then you can use it in the formula:
    =INDIRECT(”AM”&ROW())="R"

    Kind regards,
    Bastien Mensink

  59. Thanks a lot.

  60. thank you a lot for this post.

  61. Thanks……….
    Was looking for this for long time……

  62. Hello Bastien,

    Thanks for this post, it has been incredible helpful to me. Took me a little while to realize that I had to insert quotes when setting an own value.

    After almost 5 years still helpful!
    Thanks, again!

    Cheers,
    Hein

  63. Is there any possible way to make this happen if instead of the value being "true" or "0", it's anything i put on the cell??
    Thank you

  64. Hello Andric,

    Yes you can. The possibilities are almost endless.
    For example if you want cell A1 to be colored when it is isn't empty, you can use the following formula as conditional formatting:
    =ISBLANK(A1)=FALSE

    The following article might be interesting to you:
    http://excel.tips.net/Pages/T002800_Shading_a_Cell_Until_Something_is_Entered.html

    Kind regards,
    Bastien Mensink

  65. Olá Bastien Mensink!

    Não estou conseguindo, deixar a linha completa de todos funcionarios DENITIDO(S) (A1) e (A3) com cor Vermelha, usando =indireto("A"&Row())=Demitido

    A B C
    Situação Adm/Demissão Funcionário

    1 Demitido 01/08/2010 Roberto
    2 Ativo 01/05/2007 Maria
    3 Demitido 15/09/2010 José
    4 Ativo 05/02/2005 João

    OBS: Caso possa fazer um exemplo no excel 2007, e enviar para meu e-mail.

    Desde já, antecipo meus agradecimento.

    ABN Barbosa

  66. Thanks for this blog post. Was really helpful with a spreadsheet I was working with. Keep up the good work!

  67. It is really great but can you tell me if I want to color the full row for a specific number or text…I think you are not getting what I want to say…it's like..
    ** If my sheet found any "0" or say text like " mona" in a row it will color the full row yellow…..
    ** If my sheet found any "1" or say text like " lona" in a row it will color the full row green…..

    Is it possible with conditional formating????? I am searching it for a long time..

  68. can anyone tell me how to make row change color if i put date (12.12.2009) in cell???
    i tried this:
    =INDIRECT(”C”&ROW())=DATE
    =INDIRECT(”C”&ROW())=dd/mm/yyyy and similar,
    but nothing is working?
    please i need it as soon as possible

  69. @ahmed
    If Col C only has dates in it and you want them to be colored when this is keyed in try
    =INDIRECT(”C”&ROW())>1

    this will colour it when you key in any numeric value greater than one and as excel stores dates as numbers it will colour this when a date on or after the 2/1/1900 is keyed in, this can be also be used to only highlight cells after a certain date.

    =INDIRECT(”C”&ROW()>40477 will colour cells with a date from today onwards. to find out what numbers dates convert to key in the date and then change the cell format to number.

  70. This page was SO helpful THANK YOU!!!

  71. Can this function help with finding duplicate value in a column and color the rows with duplicate columns. So if c1=c2 then one color for the whole row, if c2 is not equal to c3 then second colr

  72. Hello Bobby,

    Yes, it surely can.
    The following article from MS shows how you can use conditional formatting to find duplicates.
    It uses that =COUNTIF() formula to do that:
    http://office.microsoft.com/en-us/excel-help/locate-duplicates-by-using-conditional-formatting-HA001136616.aspx

    Note:
    The =COUNTIF() function as a few limitations. It only compares the first 15 digits or numbers so it wouldn't work for creditcardnumbers because it believes that 4916685766246816 and 4916685766246813 are equal. And it returns an error if one of the cells has a length of more then 255 characters.
    The article below is about Excel 2000, but these limits are still in Excel 2010:
    http://support.microsoft.com/kb/231114
    Usually you won't run into this limits, but it might be good to know that they are there.

    If you have such numbers or large values then you can use the "duplicate" tools from ASAP Utilities because those do not have that limitation.

    Kind regards,
    Bastien Mensink

  73. hi,
    I want to color a cell based on the text in the adjacent left cell.
    ie, if cell C1 has the text "Closed", i want the cell D1 to be in green color.
    And this needs to be applied to all the cells in column C and D.
    Is this possible?

  74. Hi,

    I am trying to change the colour of 5 cells which are in a row, when the value of another cell is equal to a certain phrase.

    For Example:

    If Cell B1 contains the phrase Existing Accounts the colour of Cells S1:W1 change

    Can you help?

    Thanks
    Elliot

  75. Hello Angel and Elliot,

    Both of these can be done.
    If you want a cell to change color, you have to set up the conditional formatting in that cell.
    I don't have the time to work out specific examples for your but if you experiment with it and take a look at the mentioned websites I'm sure you'll get it working.

    Kind regards,
    Bastien Mensink

  76. I don't know why it took so long and I had to wade through all kinds of unhelpful websites till I finally found your "one in a million"! It worked immediately and I am so thankful! I never saw anyone else refer to this INDIRECT function. THANK YOU THANK YOU THANK YOU!!!!!

  77. @ Helen: The formula would need to include AND(Indirect(…)30), with the “…” being your reference cell formula.

    @ Marc: You would need to have something like the Index function if the cells are not the same, but if you are trying to make it for the cell itself, you can use isblank(indirect(“E”&row()))

    @ Neil: You just need a simple, if the cell with the date is greater than now() in the conditional field. Something like indirect(“C”&row())>now()

    @Nick: When you say =R, R needs to be in double quotes

    @Angel: Just put in indirect(“C”&row())=”Closed” as your argument. Make sure you have set your formatting to what you need it to be.

    @Elliot: That is super, super easy. Say the cell is B5, your argument would be $B$5=”Existing Accounts”

    @ everyone/all readers
    Bastien is NOT here to solve all of your problems for you. He has created a helpful site that shows a technique not many even know about. Be grateful and do a bit of trial and error on your own spreadsheets. I have seen many of these equations that everyone is asking for easy to figure out with a bit of trying. Just remember that your argument must return true or false. If you cannot put IF() around it, it isn’t going to work.

    @ Bastien: Thank you. I was looking for a way to do Index() in it and I was not quite understanding how conditional formatting worked. I really appreciate the instruction.

  78. I am wanting to auto Change the text color from red to black in Col A rows 2 through 33 based on the value of Columns B, C, E and G rows 2 – 33 being greater than 0.

    How do I go about doing this? I don't want this format in any other cells/rows except the ones stated above.

    I'm not excel savy so please explain in detail lol.

    Thanks in advance!

  79. […] some knowledge of writing formulas and knowledge of the INDIRECT function. Bastien blogged about Conditional row color based on a cell value which illustrates the process quite nicely. The steps I am about to cover mimic Bastien's […]

  80. jeez. thank you. took me forever to find this answer on google. should be top results.

  81. BRILLIANT! Thank you so much for posting this. I've searched all over for the answer to this question, and your solution is the only one I've seen that works. It works!

  82. All great stuff…

    Any help with this one would be greatly appreciated…

    I have been using the =INDIRECT("A"&ROW())="variable" rule in the past to colour an entire row, with 'variable' being whatever cell content I want to command the rule.

    I now want to apply the same rule, but only if either an odd or even number is in the cell, rather than stating a fixed variable.

    Any thoughts???

    Thanks

  83. AWESOME! Months of searching for a way on my own to do this and viola'. My answer is right here. I had to play but here's what I did (Oh, I'm no expert by far):
    Excel 2003, conditional formatting, spreadsheet of text, numbers and dates with headered columns and named rows

    I wanted to shade an entire row of data (the data only) if the text in a specific cell (column based) in that row contained specific text.

    The formula that worked for the conditional formatting is =INDIRECT("column letter"&ROW())="text"

    I highlighted my entire data table with header row;
    ALT+O (format), D (conditional);
    Condition 1: FORMULA IS;
    =INDIRECT("C"&ROW())="Not";
    Click the FORMAT button and set PATTERN desired;
    Click OK;
    Click OK.

    Explanation:
    =indirect – Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.

    ("C"&ROW()) – the "C" is the column label, not the header title. The &ROW will apply the format to the entire row and the () limits the format to within your highlighted area
    ="text" – the text is the content of the cell in column C. In my case, it looked like ="Not" where Not is the text I enter into the cell to close out the row.

    Now when I open my spreadsheet, all of the closed out rows with Not in column C are highlighted, or colored to give the quick representation. I still filter my data table, but this allows the overall appraisal of progress.

    I hope this helps someone else like me.

    Thanks to all of the contributors to this post,
    jomobe

  84. Hey WAZ,

    Try using =MOD($A1,2)=1

    You will be abe to set your formatted area by highlighting your cell array first;
    MOD is an odd one but works for what you stated;
    $A1 will look at the cell with your numbers based on the entire column and the ,2 I'm not sure because 0 and 1 do not work at all;
    the =1 will format the entire row of data that an odd number shows up in and =0 will for mat the row for even numbers found. Again, 2,3,4,… does not work at all.

    Hope this helps …
    jomobe

  85. tnx.. . it really helps! now i can make my worksheet more comprehensible, time saving and more conveying!

  86. I have a spreadsheet and when i enter something into one column i want to change the color of the entire row.
    they are currently yellow until that column has something entered into it which i put in manually and would like it to do it automatically.
    please if anyone knows how to do it and give me exact instructions to follow that would be so helpful.
    thanks!

  87. Hi,

    I need to highlight a row if the difference between two column ( column will have dates ) is greater than 1. Also I need to replace today's date if the second ( date ) column is empty and it should be hidden.. Please suggest.

    Best regards,

    Hari Bhaskar.

  88. hello!

    jomobe, your post was really useful and im mch closer to getting what i want…but not quite…maybe someone can help!

    i used the exact formula, but replaced the "not" with "n"

    =INDIRECT("C"&ROW())="N";

    what i want though, is for the entire row to be highlighted if the cell in coloums C *AND* the cell in coloumn D ="N" …
    Basically, its not enough for the row to be highlighted if its just one N…

    im not that experienced with excel and have tried everything i know, so any help would be HUGELY appreciated!

    thanks!

    Dana
    i hope that makes sen

  89. Hello

    I used the formula

    =INDIRECT("J"&ROW())="YES" and made it go yellow

    however i want to do another saying

    =INDIRECT("L"&ROW())="YES" and made it go red

    with the second overwriting the first how do i make this work?

    thanks

    Stuart

  90. in Excel 2007 I need to conditally format a row (fill with color) based on text rather than a numeric value. I have one worksheet and I need the rows highlighted with differernt color based on Approved,not approved, I can't seem to get the formula right to get the entire row highlighted. When I say "Approved " it only highlights that cell and not the entire row… Any suggestions pl let me know.

  91. Hello Varun,

    Thank you for your feedback.
    If you want the entire row to be colored, then you have to set the conditional formatting for each cell in that row.
    Just select the entire row and then set the conditional formatting conditions.

    Kind regards,
    Bastien

  92. Hi

    I have a problem which i still havent been able to overcome. I am using excel 2007.

    i need the entire row to be highlighted by a specific colour based on the value of one cell in that row. Ex. if the cell contains the text "completed", i would want the entire row to get colored.

    I could do this using conditional formatting using value of the cell option for the entire row to get highlighted.

    PROB: i want this conditional formatting to be repeated in all the rows below that too… Have a couple of hundred rows, and this keeps getting added. Is there any way to copy the conditional formatting to below rows too??

    Very much in need of help.

    Thanks,

    Sufyan

  93. Hello Sufyan,

    Thank you for your feedback.

    Yes, you can copy the conditional formatting, just like normal formatting.
    Just copy the row, select the target row and choose in the Excel menu Edit > Paste special > Formats
    or in the Excel ribbon Home tab > Paste > Paste special > Formats

    Kind regards,
    Bastien Mensink

  94. Hi Bastien,
    I am using 2003, Ihave created a drop down list with four options & i want all the 4 in diff colours. Whenever any option is selected it has to appear with the respective assigned colour. Kindly assist.

  95. Hello Mayur,

    Thank you for your comment.
    I'm sorry but that is not possible, neither in Excel or via ASAP Utilities.

    Kind regards,
    Bastien Mensink

  96. Thanks.
    Great tip.
    Solved issue for me.

  97. For the poster wanting to know how to format an entire column based on one row's value in that column, I got it to work. If you want to format the column something whenever the column value in row 1 = "H", use this for the conditional formula:
    =A$1="H"

    if you're looking in row 2, use:
    =A$2="H"

    etc

  98. Im trying to apply conditional formatting in Excel 2010 for a defect report. I’m tryi8ng to change color of row based on the values present under the status column. For instance if the status value (in my case Column I) = “In progress” then im trying to make the entire row that has the value green.

    I tried this selected the entire report and used this formula under Conditional Formatting ->New Rule->Use formula to determine which cell to format->Formula values where this value is true and entered the below value

    =INDIRECT("I"&ROW())=Closed

    This did not work for me , Can you please help me with this issue?

  99. Hello Sharan,

    Thank you for your feedback.
    You should put the text in quotes: =INDIRECT("I"&ROW())="Closed"
    You can download an example workbook from:
    http://www.asap-utilities.com/blog/wp-content/Highlight-closed.xlsx

    Kind regards,
    Bastien Mensink

  100. It worked. Thanks a lot for following up!

    Regards,
    Sharanya.

  101. I just wanted to start by saying thank you for this very informative post! This has saved me from very redundant work.

    I do have a question and hopefully someone can answer. I am able to get this to work but what if I'm needing to have a row highlighted containing "Missing Information: Etc Etc Etc" and the row highlights because it includes the text "Missing Information" ON TOP of everything else included into the cell. I find that the current format =INDIRECT(”C”&ROW())="Missing Information" will only highlight when the cell equals "Missing Information" rather than containing it and additional text.

    I hope this is clear enough to understand.

    Thank you!!

  102. Thanks a lot. Great tip.

  103. Thanks a lot, works wonderfully :-)

  104. This is amazing and you are my hero. This is going into my queue with vlookups and sumifs.

  105. Works great for me.

    Now I just need to know how to do exactly the same but for columns instead of rows.

    What would the formula be for that?

    Paul

  106. I had a pretty complex excel sheet and tried to apply this with many other attempts of conditional formatting and found after about 20 minutes I found out I had to use something similar to:

    =INDIRECT("$C"&ROW())=TRUE

  107. I hvae been searching for the formula to get an individual cell to change color dependant upon the test in the cell IE if a cell in row G contains the word APPLE i want that cell and any others in the column with the same text to be red. I have not found anything that fits this description yet, I have used the following to make it work for a date,
    flormula is =ISBLANK(E1:E100)=TRUE
    CELL VALUE IS LESS THAN =NOW()
    CELL VALUE IS LESS THAN =NOW()+30 (MEANING IN TEH NEXT 30 DAYS)

    ANY HELP IS A PPRECIADTE.

  108. I want to do conditional formatting based on cell color.Means, if the cell color is green, should get a comment Closed, if red, cancelled & if white, should get open.how to do it?

  109. I have tried the origanal formula but cant get it to work on my worksheet. I am using 2007. In column Q when i type VOID into Q4, Q5, Q6 for example i want the whole row to turn a different colour. Can someone provide me with the correct formula to try? Thanks!

Pinterest