Comments on: How to change the row color based on a cell value in Excel

Learn how to quickly change the color of the entire row based on a single cell's value in your Excel worksheets. Tips and formula examples for number and text values. Continue reading →

Comments page 7. Total comments: 546

  1. Hi Svetlana,

    Can you help please?

    I have seen above the following

    "I have one column (J) which is "date of sale". This only has a value if a sale has been made. So, I want to highlight all the rows which have a date in column J (essentially highlighting all the rows where I have made a sale, and leaving the ones where the sale has not been closed blank) with your response of Try selecting the whole table and then create the formatting rule with this formula =$J2"" (assuming that your table has headers). It will color all the rows that have any value in column J."

    I have a similar issue but its for 2 columns (E and H) but a date will only be in on or the other - never both. hope this makes sense.

    would really appreciated your help please :-)

    i am trying to make this work with dates in either column e or f

    Sorry to trouble you, but I cannot make this work for a date!
    I have one column (J) which is "date of sale". This only has a value if a sale has been made. So, I want to highlight all the rows which have a date in column J (essentially highlighting all the rows where I have made a sale, and leaving the ones where the sale has not been closed blank)
    Can you help?
    Thanks!

  2. Thank you
    I found the solution.
    =ISNUMBER(Search("p1",S3))
    This could be applied to all payments.

  3. Payment 1 Payment 2 Payment 3 Payment 4 Paid 1, 2, 3, 4

  4. Hi!
    Thank you for this site that produces excellent information.
    I would request your help!
    I have a table and one cell will contain (Paid 1, Paid 2, Paid 3).Each will be written after one month. These refer to other cells of payments. (payment 1) (Payment 2) and Payment 3). So I need each cell of payment to be highlighted if I write (Paid 1, Paid 2, Paid 3) in the last cell.
    Appreciate your help
    Imad

  5. if a column contains "any date" i want to fill a color to the entire row in excel

    1. Hello Naseef,

      Select the rows that you wan to highlight, and create a conditional formatting rule with the below formula, where A2 is the top-most cell with a date:
      =ISNUMBER($A2)

  6. very helpful thank you!

  7. I am sorry. My question was a bit confusion. Here is the revised one: Are we only able to check one column at once? Is it possible to check entire worksheet for a keyword(s) and then highlight all the rows have hit in different columns?

    1. Hi Mike,

      I don't know a way to check the entire worksheet. However, if there are a limited number of columns to check, you can use the following formula to highlight rows that contain a specific text in any of the concatenated columns:
      =SEARCH("text",$A1&$B1&$C1&$D1)>0

      Where 1 is the top row that you want to highlight.

  8. Are we able to check one column as once? Is it possible to check entire worksheet for a keyword(s) and then highlight all the rows have hit in different columns?

  9. Hi,
    I have a table where the text in column N is either "Live" or "Off-hired" and I would like the whole row to be highlighted whenever "live" occurs. I have tried a number of the formulas above but I can't seem to get it to work.
    please can you help?
    Thank you very much :)

  10. Is it possible to insert icon sets from different sheet in the same workbook

  11. Hi,

    I need to change the cell color based on the previous cell value.

    For example
    A B
    1
    2

    If B2 value is greater than A2, then B2 value automatically change into green color. If it is less than A2, then I need to change into red color like wise it will need to happen when C2>B2, D2>C2 etc…

    Is there any way to do this?

    Thank you for your help.

  12. Thank you very much. This is exactly what I was digging in Microsoft help center for 30 min.

  13. hi there,

    its very nice and helpfull,

    Thanks,

  14. Hi there,

    so on my Excel, I have color shaded for the status, green for good, red for bad, etc.

    Now I need to change those colors to a status value in text i.e. good, bad, opportunity for a migration to a CRM?

    is there an easy way to do this for several hunrded entries on Excel?

    thanks

  15. Can you please help me, I'm working on a spreadsheet and I want my J4:AI4 to change it font color to red with the following condition: if column G = 10, column I < 17 and column G = 20, column I <7 and the even rows from J6 to J1154 < 0.

  16. I have a spreadsheet that is currently in red font. In column F an x is placed if that row has been edited. Is there a formula that can be used so that when the x is placed in column F the entire row changes to a black color font?

  17. I have four columns. The first one has a name and the next three have numbers. If the first three columns are all 0 I want the name to turn red. Only if they are all 0 though. If just 1 is above 0 it needs to turn yellow. If they are all above 0 then it needs to turn green. Is there any way I can do this?

  18. I have three columns ("Invoice Inc. VAT", "Credit Inc. VAT" and "Balance Inc. VAT").
    I would like to have the values in the "Balance Inc. VAT" column to be displayed in red when the other two columns ("Invoice Inc. VAT" and "Credit Inc. VAT") are not equal.
    When the values in the "Invoice Inc. VAT" and the "Credit Inc. VAT" columns are equal, I would like the values in the "Balance Inc. VAT" column to be displayed in black.
    I have tried two Conditional Formatting rules (one for "Less Than" and one for "Equal To") but all the values in the "Balance Inc. VAT" column just show red.
    Pleas can you help?

  19. I have three tabs, one (Definition Tab) having definitions like Small, Medium, Large, & X Large and Time to complete work for each size in weeks are 3, 4, 6 and 8 respectively. The second tab (Forecast Tab)has weeks in columns starting week 1 till week 53 of an year and 4 Rows as Small, Medium, Large and X Large. The third tab (Customer Tab) has customer name, Size, resource name, work start week, delay if any, total weeks as columns and each row represent different customers.

    I want to have the rows in second tab (Forecast Tab) highlighted with colors. For eg: if size of a customer is Large (6 Weeks), work start week column is having value 8, & delay if any has value 3, 9 (6+3) columns against a newly added row in forecast tab need to be highlighted with Yellow automatically. Like wise, any input modification done to the customer tab, based on definitions, the forecast tab should get a row added under the weeks accordingly.

    Appreciate your help is getting this using Excel 2010 or above.

    thanks in advance.
    Krish

  20. I want to change the font color of cells if the Status is "NO REPLY". How will I do that?
    CUSTOMER NAME PRODUCTS PIC STATUS
    ABC 123 JOHN NO REPLY
    DEF 456 MIKE PLACED AN ORDER

  21. I Need help from u
    i wants color to colum in 1-10 then after 10-20colum i needed to give another color how can i?

  22. I have two columns with dates. I have shaded the first column with some specific dates. I want to shade same dates in the second columns with the help of some formulas.

  23. Thank you

  24. Hi,

    I use excel to create daily reports for my team. It involves copying data from an internal site and pasting it into excel so i can manipulate it and make it useful for us. The problem is the data comes with links (which I need). This ruins any conditional formatting I may have on the template used to create the reports.

    Is it possible to keep the source formatting and have text in a single cell change the color of a row?

  25. HI, i have data in two column in one excel sheet, now in another sheet i am linking the same data by clicking + sign. But Data is linking but the problem is: when i changed cell color in one sheet its not changing in another excel sheet particularly for that cells only

    pl help

  26. Peoples i need help as i suck with excel.

    I would like a row to be highlighted if there is nothing in the last two cells of that row. However there would be data in each of the other cells in said row.

  27. Hi Svetlana,
    there are two things GOOD & BAD (say 2 ROWS)
    I want a formula that will make "BAD" row Backgroud RED in color and NO COLOR IF GOOD
    So Is it possible that certain formula for colors exist?
    Regards,
    Savinn

  28. Apologies. The formatting formula we are using is "$C2>0", cheers. Z

  29. Dear Sir,

    This is a very valuable post. I learned a lot from it.

    I was wondering if there is a way to change the color of a cell "Only" when the value of the sell change from a certain value to another, without changing other cells that got same value under the same formula.

    I will try to explain better: I have "Stock" column which has only one of two values in its celle: either "0" or "10".

    Items that are out of stock have value of "0" while any other item that is in stock (or comes back to stock) get value updated to "10" because we never re-stock items less than 10 units minimum in quantity.

    My issue is not by having items becoming out of stock because we have a control on this. The issue is that some items come back to stock but we never notice early, therefore we miss the chance to increase the quantity as the quantity will remain zero in the marketplaces we are selling our items on.

    With your code, I was able to use formula "$C2<0", however, it changes to color to all items that we have "10" in quantity, regardless whether they were already having "10" or got updated from zero to ten, making your formatting formula not very beneficial to me.

    Is there a way to highlight only the cells that specifically get values updated from zero to ten without affecting the cells that already got value of "10" in first place?

    I look forward to hearing from you or anyone else who has the knowledge of dealing with this matter.

    Best regards,
    Zed Sefi

  30. I am using the following formula that I pieced together after some research online. It seems to be working in the example I tried it on, but I don't fully understand the entire formula. I want to interpret so I can figure out whether or not it will work for similar checks and balances. The formula and reason are as follows:
    =AND(ISBLANK($D5)=FALSE,NOT(ISNUMBER(MATCH($D5,$E5,0))))

    Looking for a cond format that will fill column D if its respective C cell is not the same value/text (d1="x" and c1=""), but will not fill is column d is blank, d and c are blank, and d and c match.

    Can you help interpret the formula and if it will work for an entire spreadsheet comparing 2 columns (separate conditional formats)?

  31. Hi Svetlana

    Not from want of trying, I have failed to have a colour of a row changed from red to green if column F has a number in it

    On another sheet I would like to mark the whole row as green if the number in column R is greater than 65.

    Please help

    Darcy

  32. How to change automatically the text color based on a cell's matched text in Excel

  33. pleas sand me condition formatting function.
    IF
    AND
    IF(AAAA,)AND)

  34. Hi
    If I want to highlight any cell in the column that contains a number larger than the previous one in the same column, what should I do?

    1. Hello, Gloria,

      Please try the following:
      Go to Conditional Formatting -> New Rule -> Use a formula to determine which cells to format
      Enter the following formula:
      =AND(ROW(A1) <> 1, A1>OFFSET(A1, -1, 0))

      Hope this helps.

      1. Hello Maria

        Thank you for your above explanation.

        My predicament is the same as Gloria's "If I want to highlight any cell in the column that contains a number larger than the previous one in the same column, what should I do?"
        I have tried your formula and I cannot seem to get it to highlight the cell.
        In the example below, I would like to see -11.41 and +3.09 highlighted, because in both instances the cell above it, is >10 value points away from it.

        -33.22
        -26.75
        -22.19
        -11.41
        -8.21
        +3.09
        +8.03

        What would my formula be? To make it easier for me to work with, the nominated cell numbers are:-
        -11.41 is H51
        and
        +3.09 is H53

        I hope I am making sense :)
        Many thanks

        David

  35. Is it possible to use conditional formatting to put a coloured border underneath a row where the date in column A changes.

    So if you had a table with:
    14-01-16
    14-01-16
    15-01-16
    16-01-16
    It would put a different coloured border under each different (unique) date.

  36. Hi
    I want to color my a row which contain "E" in a cell of that row.
    please provide me a solution for my problem

  37. I want to colour my employee id numbers with red and green which is randomly upto 1200 numbers anybodycan you help me to make conditional format on this

    1. Hello Rathna!

      Please use the Home Tab -> Condition Formatting -> Color Scales command

      You can use the "More Color Scale Rules" additional command to define a custom "2-Color Scale" format style.

  38. Hi,
    I want to colour my bar chart "bars" based on the text written inside the bar chart "bars". ( ie if "A" is written in first bar- the bar colour should turn to red, if "B", then blue and so on), the text in the bar is dynamic and changes with the data.
    Can you please advise.
    Thanks
    Sam

      1. Thanks Fedor.
        Actually I want the different "bars" within Bar chart to change colour automatically based on "Text" written within the "Bars".
        ie if the text entered in the "Bar" is North, the "Bar" colour should be filled with "yellow" colour, and so on.
        Thanks again.

  39. Hi,

    1-3,7-9,13-15 till last row how can change "Bold" in VBA
    4-6,10-12,16-18 till last row how can change "Italic" in VBA

    Please give me suggestions.

  40. Hi, I am trying to conditionally format cells in one column based on overdue dates in another column. Specifically, I want cells in column A to turn red if the date in column P (corresponding row) is past due. When I set my conditional formatting for column A, this is the formula I have inputted: =$P2=TODAY . I also want to set up a second rule for due dates 90 days out, where the corresponding row in A turns blue. This is the formula I have inputted for this conditional format: =$P2<TODAY+90 . Neither format is working however, so I know I'm doing something wrong. Can you point me in the right direction? Thank you!

  41. Hi There, Can someone please help me with a conditional formatting to change a whole line colour based upon whether its Saturday or Sunday.

    I am using =E12+1 where E12 is a date 01/01/16 to have my cell look like this Saturday, 2 January 2016

    I had supposed using =SEARCH("Saturday",$E13)>0 to change the whole line but obviously the formula for dating does not contain "Saturday"

  42. You have saved Preston. Thank you.

  43. Hello All,

    i am facing one issue:

    suppose in a row, there is one box with green color.

    what formula should i use for this to know that in row we have different color?

  44. Hi,
    I am trying to conditionally format a row which is reference another cell.
    EX - IF A1 is some number like(1/2/3/4) & B1 is YES/NO.
    if in A1(1)=B1(No) then A1 become condition by color & if Yes another color.
    Help

  45. What formula would I use, if I want the text of a row to be red if one particular cell is blank and another is not blank in said row.

  46. Hi Svetlana,
    I am trying to conditionally format a row of numbers based on a manually entered number in cell c4 my data is from cell A11 to I39 for ex if I enter a random number between 10 & 24 in cell C4 I would like the corresponding row to be a different color, I have managed to do this but it will only format the FIXED numbers from A11 to A39. Thanks in advance for any help you can give me its driving me crazy..

  47. I'm trying to make a cell match the color of another cell. I want A2 to match K2 (which is a date) the same color. K2 is formatted to change to red when it is 90 days out from today.

  48. Hi Svetlana,

    Im having some problem. Let say my table is as follow:

    A B C D E F G
    1 2 3 4 5
    6 7 8 9 10

    I want to change the cell's colour based on the other cell value, for example:

    A B
    1 DONE
    2 NOT
    3 DONE
    4 DONE
    5 DONE

    If the word in column B is 'done", the cell 1 in the upper table will turn blue, let say. How to do it? The sequence of the numbers in upper table also not in order. It involve a lot of numbers and im not able to do it cell by cell. Please help me..

  49. Hi Svetlana,

    thank ypu very much for your post. Really useful. I just have the problem that saving and reopening the file,it doesn't keep the conditional formatting set. Do you know whay it might be?

    Thank you very much in advance,
    Valeria

  50. I am trying out this example on my Excel 2013 and I get an error that "we found a problem with this formula.."
    I have defined a rule with the formula below:
    =OR($D2="Due in 1 days",$D2="Due in 7 days")

    1. Hello Boniface,

      Most likely the problem is in a different List Separator. This formula is written with a comma, which is the default List Separator in North America and some other countries. In European countries the comma is reserved as the Decimal Symbol and the List Separator is set to semicolon. So, try replacing "," with ";" like this:

      =OR($D2="Due in 1 days";$D2="Due in 7 days")

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)