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 2. Total comments: 546

  1. Useful tip. It did just what I wanted (Highlight entire row depending on one cell value) . Thanks.

  2. Excellent help page. Thank you.

  3. Hello,
    Is it possible to create a formula to number cells, but only the ones, which have one color (e.g. only white ones)?
    + make formula automatically 'refresh' when we decide to change the color of one or two more rows?

    KR,
    Natalia

    1. Hello!
      You can get the cell color in Excel using the user-defined function GetCellColor.
      To numbering cells with a specific color in column A, you can use something like this formula starting from cell B2

      =IF(COUNT($B$1:B1)=SUM(--("#"&DEC2HEX(GetCellColor($A$2:A2))="#FFFFFF")), "", SUM(--("#"&DEC2HEX(GetCellColor($A$2:A2))="#FFFFFF")))

      I hope it’ll be helpful.

  4. Hi

    i want to Highlight row if cell starts with specific text in spreadsheet online.
    can anyone help me about it?

      1. Following these to the T does not work. It works if applied to the column only, but cannot get the whole row highlighted :-(

  5. Hello There,

    I own a food product business. I Have a worksheet where I keep track of deliveries. In the worksheet, against each item, I record Delivery Status(Column K) and Payment Status (Column L). My requirement is two fold.

    Rule 1. When Item is delivered, but payment is yet to be received from customer, I want the row colour to change to yellow based on the text "y" in Column K. In this case, corresponding cell in Column K is Blank.

    Rule 2. When Payment is received from customer, , I want the row colour to change to green based on the text "y" in Column L. In this case, Column L as well as Column L contain the text "y".

    Rule 2 should be applied by overpowering Rule 1.

    I am able to set up the rules individually, but I am not yet able to make rule 2 being able to overpower rule 1.

    Thanks in advance for your advice.

    Regards,
    Rucha Mulay

  6. I want to use conditional format to set the background color for each cell in the B column based on the data in the O Column.

    My formula: Formula=$O$2="Cancelled" Format (black color) Applies to =$B$2
    Formula =$O$2="Delivered" Format Blue color Applies to $B$2.

    I want to be able to select all the cells in Column O and Column B and have the colors change. But this doesn't work. I have to set the 5 different options and type them for every cell.

  7. (cell 1, row 1, col 1)(cell 2, row 1, col 2)(cell 3, row 1, col 3)
    I understand the part of conditionally formatting a single cell, but combinations I need more help on.

    I enter the text 3777 in (cell 1, row 1, col 1) and I give it a red background in conditional formatting.

    With the 3772 entered, (cell 2, row 1, col 2)(cell 3, col 3) also are given red backgrounds because I entered the 3777.

    Hope this makes sense to you, Appreciate any help

    1. Hi!
      Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following conditional formatting formula:

      =(A1=3777)*(B1 < > "")

      Pay attention to the following paragraph of the article above: How to change a cell's color based on a value of another cell.

    2. 3772 meant to be 3777... sorry

  8. This is very useful for a lot of people including me. Keep up the good work. Thank you so much, you are saving a lot of time of us.

    1. Hello,
      How do I color three rows differently in a rule if a condition is met. Example:

      ROW1: S1 S2 S3 S4 S5 S6 S7
      ROW2: S1 S2 S3 S4 S5 S6 S7
      ROW3: S1 S2 S3 S4 S5 S6 S7

      The formula checks if the value of the first cell in ROW1 contains "S", if true, colors ROW1 green, ROW2 red and ROW3 blue for all data range with similar configurations irrespective of the number of columns. I appreciate your assistance.

      2. I am trying to create a shift roster.

  9. You wrote: "In the formula, E2 is the address of the cell that you want to base your formatting on, the dollar sign ($) is used to apply the formula to the entire row..."

    This is not entirely correct. Dollar sign is used to turn a relative cell address into an absolute address. The formula for the whole range is based on the formula for the top-left cell and is modified behind the scenes for each cell. Dollar sign anchors the reference to the cell in a specific column and/or row. In this case $E2 means that all cells in the second row will use the value of E2, but cells in row 3 will use the value of cell E3 because column E is locked, but row isn't and moves down. You can test this by changing the cell reference to $E$2. Now all the cells in all rows will be formatted based on the value of E2.

    There are 4 possible combinations for a cell reference: E2 (relative address), $E2 (absolute column, relative row), E$2 (relative column, absolute row), and $E$2 (absolute address).

    One example: say you have a formula in A1 that says: =D1

    If you than copy that formula down into B2 (changing both column and row for demonstration), you can get 4 different results depending on the type of cell address used:

    =D1 -> =E2 (B2 is 1 column to the right and 1 row down from A1, and so the target cell also moves the same distance from D1 to E2)
    =$D1 -> $D2
    =D$1 -> E$1
    =$D$1 -> $D$1

    Hope this helps :)

    1. Hi Zoran,

      Good point! I reworded that sentence.

  10. If cell value is 7 then color seven next blank or with value cell .
    Plz suggest formula

  11. i noticed that all is taking reference to a column eg. =$C2="done".
    problem is my specific_text ie. "done", could be anywhere in the data table, say $J:$L, is there a way as well?

  12. Hi Svetlana and or Alexander,

    I would like to add additional formatting/several rules to a current rule. At the moment I have a current formula for the "top 3 running in cells N2 to N20. It's currently work fine.

    My issue that there is other information that I would like to colour in columns L and M that would be associated with the top 3 highlighted cells in N.

    Do does one do this? Can it be done?

  13. Hi
    Im working on an excel sheet about preventive maintenance about vehicles a company has.
    There are two cells called the next change and the last change which the next change=last change+8000.
    There is a milestone on another sheet which gets updated every single day.
    how can I write a formula which makes the next change cell red when the KMs in the milestone get near the value in it so the staff can alert the driver?
    I need it to get updated every time the last change is updated without needing to update the formula itself!
    I hope you respond to mu question.

    1. Hello!
      If I understand your question correctly, use the rule of conditional formatting in the cell D1:

      =B1-D1<10

      B1 - next change
      D1 - milestone
      10 - how much is left until the next change. You can change it as needed.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  14. Hi, I am trying to sum a Row with different values based on the text or colour of the cell (whichever is easier). So in each row the cell will wither be blank, or be one of three colours (or text) and each colour is assigned a numerical value of 1, 2 or 3. I need a formula that will calculate these values/points. So for example in row one there are three cells filled one red and two green. Red has a value of 1 and green has a value of 3 so the formula should return 7. Thanks

  15. Hi,

    I want to change cell value based on color and the option available in conditional formatting is in reverse way. Can you suggest.

    Thank you.

  16. I am using the formula below to try and format rows of data if the number in column C happens to also be in column A of Sheet 1. However, it's only highlighting the first row that meets that condition. All of the other rows that meet the condition are not changing color. Any suggestions?

    =$C2=Sheet1!$A:$A

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =MATCH($C2,Sheet1!$A$1:$A$100,0)

      You can learn more about MATCH function in Excel in this article on our blog.

  17. HI there,

    I have an excel sheet with columns and want to add another which will have date of expiry in them. I would like that these dates are changed automatically when we get to a month before expiry date to amber and red once its past the date, how can I do this???

  18. I have to make RM coverage sheet if have 50 nos RM in stock and I am consuming daily 10 nos. Then remaining days should be shown in coloured pls. Suggest for the same

  19. Hello, Is it possible to apply the varied colors of cells to the points they're representing on a graph (via point color or label)?
    thanks

  20. I cannot get a pivot table based upon time to conditional format. I have reformatted the numbers to be "general", Time, Custom (mm:ss), text and nothing works. When I use a formula that is built-in, such as "higher than", it highlights the whole column, not distinguishing between values.

    Excel 16 Conditional Formatting of time within pivot tables is really difficult!

  21. thank you very much, it really helped.

  22. Hello Madam / Sir,

    I have a rota for our department. So, each name appears in different cell / different column, multiple times across the sheet.

    Please advice, How can I change the colour of the row depending on the name, which can appear in any cell across the sheet?

    Secondly, can I have a drop down cell with various names,,,, and depending on which name I select in that cell, can I ensure that the row with that name changes colour?

    Please advice

    Thanks

    1. Hello!
      For each name, you can use something like this conditional formatting formula. Set a different color for each name.

      =COUNTIF(2:2,"Mike")>0

      In the case of a cell reference in a formula:

      =COUNTIF(2:2,$A$1)>0

      I hope my advice will help you solve your task.

  23. I have excel sheet and one column I have renewal date. I want to highlight this row before one month
    So I can I use this conditional formatting
    Please revert on my email

  24. Hello,
    I can follow the tutorial above and get it to work on a single value on the same sheet, but I am trying to colour a row based on a cell value in a range of numbers on a 2nd sheet.
    ie, on sheet 1 & 2 are cells in column A numbered with a hash+number #1 , #2, #3 etc.
    If #2 is present in the range of A2:A5000 on Sheet 2, then colour the row with #2 only on Sheet 1.
    Any help would be greatly appreciated thank you

    1. Hello!
      Unfortunately, without seeing your data it hard to give you advice. Please specify what formula you used and what problem or error occurred. Give an example of the source data.
      It’ll help me understand it better and find a solution for you.

  25. How to I apply a color to a single text in a row in excel?
    For example: Miniature doll clothing, dress, size 1
    I want the no. 1 in red on various rows.
    Then, let's say the rest of my 20 rows, the size is size 2, so I want the no. 2 in green
    What formula do I use and how?
    Thanks

    1. Hello!
      I hope you have studied the recommendations in the above tutorial.
      Use conditional formatting with a formula. Set the text color to red when this condition is met:

      =NOT(ISERROR(SEARCH("1",A1,1)))

      For green, use the formula

      =NOT(ISERROR(SEARCH("2",A1,1)))

      I hope it’ll be helpful.

  26. Hello!

    Can you help ?
    I'm looking for solution coloring a cell, or cells regarding a third party cell value.
    Example:
    If cell A1 is 100% or greater, color cell A2:A4 with Green.

    Thanks in advance

    1. Hello Amir!
      The answer to your question is in the article above. Read carefully - How to change a row color based on a number in a single cell

  27. Particluars VALUE VALUE
    APPLE YES YES
    ORANGE NO NO
    GRAPE NO YES
    MANGO YES NO
    I want to change the color of these cells YES YES in green color, NO NO in red color, NO YES in orange color, YES NO in orange color. please assist.

  28. Hello Svetlana ...
    i have a problem with data in excel. How to
    change the color of the text to red when it exceeds 100 characters and those less than 100 characters remain black
    Thank you

  29. on the basis of blank cell in a column, I want the row being highlighted. plz guide

  30. Hi, Thank you for this detailed explanation, I was able to follow it easily. It seems mine isn't quite working properly though. We have a quote tracker, and we change the status to "Sold", "On Hold", or "Denied" in column P starting at row 5. When I change the status in cell P:5 to "Sold" it is supposed to change just row 5 to green but it changes all the rows down to row 500. My formula is =$P$5="Sold", turn to Green fill dark green text, applies to $A$5:$Q$500. There are 500 rows in our sheet and columns up to Q. If I change the formatting to =$P$5="Sold", turn to Green fill dark green text, applies to $A$5:$Q$5 it works correctly and only highlights row 5 when I change the status to "Sold". The problem with that is when I change the status to "Sold" in row 6 nothing happens. I hope I don't need to add a rule for each row?

    1. Hello Darren,
      If I understand your task correctly, please try the following formula:

      = $P5 = "Sold"

      You can learn more about Relative and absolute cell references in Excel conditional formatting rules in this article on our blog. Hope you’ll find this information helpful.

    2. I figured it out! My mistake was the fact I had =$P$5="Sold". The two dollar signs were the issue. It should be =$P5="Sold". Thanks for this tutorial!

  31. Hi, if I want to leave the cells with out a color until I fill in any data, how do I proceed with that?

  32. hi
    i need a formula which can copy a text which ahve already conditional formating and (text of the cell are in red color), to paste on other sheet which also paste the text in red color

  33. in a scenario where you want to give range for change of cell color wherein value of cell is more than 1 but less than 26. how to implement it on sheet containing a lot of values.
    thanks

  34. Hi Team,

    Can someone help me with VB code where only those rows should be highlighted which are in amber color under my excel sheet

  35. Hi
    i have different colours in rows A2,A3,A4 like Yellow, Blue and Green. When i format A1 as Red then A2,A3,A4 colour should be removed.
    Is this possible
    Thanks

  36. This will not work if the cell was set via conditional formatting. Do you have a workaround?

  37. I am working on a excel that has two sheets. I have data on the second sheet, specifically a list of names in a table. What I would like to happen is then I enter the name on first sheet, it highlights the name. I've tried a few conditional formatting attempts, but nothing has been working. Wondering what I'm doing wrong. Thanks!

  38. I have a table in excel with data. I want to apply conditional formatting to it as soon as any data in the table is changed.

    Please suggest a way

  39. What if, i have several values but i use one column to sort of group, then i want all the rows with the same "anchor" value to have same color format, is that possible? Example:
    box item comments
    1 peppers 12345
    1 potatoes 132465
    3 nails 32164
    5 napkins 231354
    1 tomatoes 1321
    3 tape 1231

    and lets say i want all the rows belonging to different box # to have the same color, i guess i could do it one by one but if we are talking of dozens of boxes per say, could there be a more automated way to do this instead of doing one condition format for each?

  40. tank you very much

  41. Hi,
    If I enter completed in "I" column, the entire column should need to change? What i need to do?

  42. Just to add to the above, the formulae IF(A2"",IF(B2"",B2,NOW()),"") seem to accomplish, but my excel sheet refreshes every 1 minute, and the moment it refreshes, the time stamp changes to the local computer time (which is current and not the time when the signal came actually).
    I even tried my own like =IF(F2="Buy",IF(F2="Sell",NOW(),"")), getting the time stamp, again when the excel refreshes, the time stamp changes to the current time. For e.g. if Buy or Sell came at say 10:15 hrs, after refreshing (let us say 2 minute refresh interval), the time reflected is 10:17 hrs.
    I only hope I have clearly stated my requirement.
    Cheers
    GK

  43. Greetings,

    Quite impressed with your responses especially the frequency of your responses, never seen any one taking so much time, which is much appreciated. Namaste !

    I have a stock trading work sheet, and there are two things I am trying to accomplish and stuggling a lot :
    (1) In my trading sheet, I have Buy and Sell in a certain column which is based on certain conditions (strategy). So these Buys and Sells appear whenever the conditions are met, in Real Time (Trading sheet takes data from the internet and continues to update every 1 minute, so there are many Buys and Sells spawning from time to time). What I am looking for is the exact time stamp (data not so required as I know the sheet is being updated today). Strangely upon scouring the net, every body talks about the same formulae viz. IF(A2"",IF(B2"",B2,NOW()),"") and this does'nt serve my purpose at all. If this is all confusing, let me give a clear example here :
    Columns A-E has data like High, Low, Close, Open etc etc
    Column F has a signal : Buy or Sell
    So all I want is some column after Column F which updates the time as and when a signal appears in the Column F. The reason is I want to see which are the new signals that appeared as I am handling 200 rows of scrips.
    OR
    (2) Can Column F which contains the signal, Buy or Sell be colored in such a manner it accomplishes the following :
    (a) New signal BUY as and when it appears should carry a different color, let us say DARK GREEN
    (b) Likewise New Signal SELL as and when it appears should carry a different color, let us say DARK RED
    (c) All signals after a time decay, say 1 minute (which if configurable would be awesome), should change color. So in the case of Buy (Dark Green), color can change to Light Green and in case of Sell (Dark Red), color can change to say Orange

    Would really appreciate if you can guide/assist me in accomplishing the above.

    Warm Regards
    GK

  44. how to use formula to if possible?
    if left side of / is greater than right side change color of the text
    1200/1000 all red text
    900/1000 all black text

  45. I'm trying to do the following:

    Conditional Format (Cell Background Color in different shades from red to green) a row when it contains a date based on a specific process in the first row, i.e. first communication, presentation, first meeting, second communication, second meeting, commitment.

    The excel would look sth like:

    Row 1: first communication, presentation, first meeting, second communication, second meeting
    Row 2: 9/1, 10/1, 15/1....

    Any help as to how I could set that up would be greatly appreciated

  46. Respected Sir,
    My problem is that, i want to count Conditional Formatted (Cell Background Color) Cell count..
    so plz help me anyone to count colored cell....
    Thanks in Advance.........

  47. can i exclude cells in the row from the conditional formatting?

    so if column B has a 1 it changes row to green, but i want to exclude column K, or M, so they keep their existing color...

  48. From the below example i need to mark highlight above 10 Days rows using conditional formatting. Can anybody suggest me which formula I can use here.

    Days: 12, Hours: 13, Minutes: 51, Seconds: 43
    Days: 11, Hours: 16, Minutes: 21, Seconds: 46
    Days: 11, Hours: 16, Minutes: 21, Seconds: 46
    Days: 11, Hours: 16, Minutes: 21, Seconds: 45
    Days: 11, Hours: 16, Minutes: 21, Seconds: 46
    Days: 42, Hours: 14, Minutes: 51, Seconds: 37
    Days: 6, Hours: 17, Minutes: 21, Seconds: 46
    Days: 33, Hours: 17, Minutes: 51, Seconds: 45
    Days: 33, Hours: 17, Minutes: 51, Seconds: 45

  49. Is there a way to have the cells highlight every time the number changes? For example I have a table that has item codes and in order to make it clearer when changing to a different item code it changes color. So if I have 3 rows with the Item code 608 then 5 rows of item code 609 then 2 rows of item 610 and so on, it will highlight the 608 items and the 610 items skipping the 609 items.

    608-highlighted
    609- not
    610- highlighted
    611- not
    612- highlighted
    and so forth

    1. For example, I have a column starting at A2 where I have my numbers (601,602... 900) and then all my data to the right. I want all even numbers highlighted (the whole row) and the odd numbers highlighted a different color.

      Go to the drop-down list for conditional formatting on the home tab and select "new rule".
      Select Use Formula to determine which cells to format
      Add the following formula

      =ISEVEN($A2)
      It is important not to have the second $ next to the numbers.

      Next to the Preview select "format"
      Select the Fill tab and select the color you want
      Select ok.
      Click on the drop down list from conditional formatting and select manage rules
      In the "applies to" section of the rule you just created, select the "data range" icon on the right and click and drag from the top corner to bottom opposing corner of the data you want highlighted.

      You can create a second rule with "=isodd($A2)" if you want to choose the other color.

      Hope this helps.

  50. Hi,

    I am trying to set up a formula that for when a cell changes text and matches another cell, it turns green. For example, if B2 and E2 both say 'strip', then E2 will turn green. But then if cell B2 then changes to say 'paint', and so does F2, then F2 will turn green. However, I still need to keep E2 green as it is a step by step process which tracks the progress of a part, so every step needs to be a green colour once completed.
    Cell B2 will automatically be updated from another workbook.

    If there is a way to do this without having to fill the cells in manually then please someone let me know as I have around 500 columns to fill in.

    Thanks :)

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 :)