Comments on: Video: Conditional formatting based on another cell

Learn how to quickly highlight entire rows based on another cell value in Excel. Continue reading →

Comments page 2. Total comments: 89

  1. Hello!
    I need to change the colour of D2 when C2 contains text.
    I have gotten as far as =ISBLANK($D2) but this performs the opposite function, highlighting the text in C2 instead of highlighting the adjacent blank cell in D2.

    Unfortunately =ISNOTBLANK($D2) doesn't work haha

    Thanks for your help!

  2. Hello,

    I would be grateful if you would assist with my question.

    Is it possible to format cells simply by reference to the active cell? For example, I would like my active cell and the four above to fill with colour. I’m trying to create a visual aid for users.

    Thank you for any help you can spare.

    1. Hello!
      Excel conditional formatting cannot determine which cell is active. Conditional formatting works with cell values. You need to use a VBA macro.

  3. Hi,

    I need help too please.

    If A1 is not blank and any cell in the range A2:A11 is bank, then I want the blank cells to be highlighted.
    I can't figure this out.

    Thank you.

      1. =($A$1"")*(A2:A11="") This worked for me, thank you. But can you explain why you use * and not AND formula?

      2. Thank you.

        Yes, it did work like a charm in excel. But I realized that my question was incomplete cause this file I'm working on is google sheet. And I did try this but it didn't work on google sheet. Is there any chance you'd have a solution for me?

        Thank you again.

        1. Hello again,

          In fact, your solution does work in Google sheets as well, but only if there is no other conditional formatting on that cell. Any work around that? The existing format I have in this range is =(A2:A11<($A$1*$b$1) to change the font color.

          Thank you.

  4. Hello Team Ablebits!

    I just wants cell A1 to be highlighted when cell B1 is blank. A1 should also be highlighted when B1 is not blank while A1 is still blank.

    Can anyone help, I know it's a conditional formatting but I don't know what the formula would be.

    Thanks in advance for your help!

      1. Just solved it. Thank for the support!

  5. Hi Team, Your Blog on MS Excel is very useful to understand the usage of functions better and helped me to improve my Excel Skills. Whenever need Solution on Excel related to issues First I will check in Ablebits blogs before looking for other options... Thanks a lot for Sharing your knowledge..

  6. Hi there,

    I'm trying to conditionally format so that if the text in column A says "Adkins", columns c, d, e, f, h, and I will highlight in that row.

    And then I want to apply a different rule to all the other cells according to the name they have in column a, and only highlight certain cells according to that name. Can you help me please??!??

    Also, I need all the rules to run in tandem as I add more lines to the sheet. Is this even possible??

    Thank you!
    Sarah

    1. Hello!
      The following tutorial should help: Excel formulas for conditional formatting based on cell value.
      Use the formula like this:

      =$A1="Adkins"

      When creating a conditional formatting rule, hold down CTRL and select the desired ranges for formatting. To make these ranges automatically change when you add data, use dynamic named ranges or column references in an Excel table.
      I hope it’ll be helpful. If something is still unclear, please feel free to ask.

  7. I have a list of items in inventory, one column (B) has number value the next column (C) has the description (i.e. case or each).
    I want column C to go blank when the value of column B is zero.
    Can anyone help, i think it's a conditional formatting but i don't know what the formula would be.

  8. I want the cell in column B to highlight if there is no data in Column G or H and the text does not include “Mon”, “Wednesday” or “Friday” in column b.

    Is that possible?

  9. Life saver! Thank you so much.

  10. What if I want to highlight only 1 cell adjacent to a blank cell ?

  11. Hello, please can you help. I'd like the cell to be red if cells in col G are blank and cells in col E have an invoice number (the invoice number is different in each cell. I did use =AND($G4="",$E4="ARI00004") but can you let me know how to not use specific text please?

  12. Hi, i am looking for a solution i.e
    I have a work sheet. one of the cell have .33 +/-.010. Next to this cell when entered value should highlight as below
    If value is between 0.3200-0.3225 should be yellow color
    If Value is between 0.3375-0.3400 should be yellow color
    If Value is More than 0.3400 should be red color
    If Value is Less than 0.3225 should be red color
    If Value is between 0.3225 & 0.3375 should be Green color.
    Similarly i have multiple cells with values in the same sheet. i am looking for a solution with single formula to highlight similarly.

    Thanks in advance.
    Regards,
    Subbu

  13. How do I condition a specific cell to highlight red, in response to a random cell within a table on a different page highlighting red as well?
    Explanation: I have a table on a separate tab that has cells turning red when certain conditions are met. When these cells go red, I need a specific cell on the main tab to go red as well. When no cells are red, I don't want it highlighted red either.

    Thanks in advance for your help!

    1. Hello Spencer,
      I'm afraid there is no way to refer to a color as a conditional formatting criterion in Excel. The easiest way to go would be using the same condition as you have in your original rule and applying it to your cell in the main table.

  14. Hi Irina, first of all thanks for halpfull videos, and learniing notes.
    I have one question about conditional formatting, I'm interested in how can I find, for exampre 10 greater number from larg excell cell ?
    thank you !

    1. Hello Giorgi,

      Thanks a lot for your kind feedback!

      Could you describe your task in more detail: are you trying to compare numbers to a certain cell and find those that are greater by 10 or more, or are you trying to highlight all cells that are greater than 10?

      I'll do my best to assist you.

      1. Hello Irina,
        I've solved that task, I was interested about compare numbers to a certain cell and find those that are greater by 10 or more, I find it easily at "conditional formatting"
        lots of thanks for you!

        1. Thank you very much for the update, Giorgi, great to hear that you found what you needed!

          Feel free to contact me if I can help in any way.

  15. Hi - this site has been SO helpful for me!

    Unfortunately, I can't seem to get this to work for my specific use case.

    I'm trying to format cells in column R based on results in Column Z, where Column Z contains a vlookup.

    When I try the simple formula in Cond. Formatting "$Z9>0" it is returning ALL columns, even those that show as nothing in column Z.

    The Vlookup formula in Z is "IFERROR(VLOOKUP(E9, KAM_SFG, 14, FALSE)"

    Nothing I do seems to make this work. :(

    1. Hi, happy to hear you find our site helpful!

      It looks like you can slightly modify the conditional formatting formula to make it ignore blanks, try this way:
      =AND($Z9<>"",$Z9>0)

      Hope it helps!

  16. Wonderful site, I'm so glad I found it. I print a morning sheet that I want to highlight select rows which already have a value entered. So I need to do conditional formatting with each row. Is there any fast way to copy paste conditional formatting to Rowe's like you can with common formulas ?

    1. Hello John,
      Thank you for your feedback.
      You can apply the rule to all your rows and add an absolute reference to the column with values you want to check. For example, you can select the range with all your data and create a rule with a custom formula
      =$K2<>""
      It will highlight the rows that have a value in column K. If your task is different, please describe it in more detail.

  17. Hey, Irina, Thank you very much for all these awesome blogs. I have learnt many tips & tricks from here everyday. Thanks a lot for you & your teammates.

  18. HI thank you for the great post. I am wondering to know that how keep rows highlighted for certain period? For example: Payment due date is on 30the, of each month and want the rows remain highlighted from 25 to 30 of the month.
    Thank you,

    1. Hello Ali,

      Thank you very much for your feedback. If we understand your task correctly and you have a column with dates, then you can select the rows you want to highlight and create a rule with the following formula:
      =DAY($C2)>24

      Here column C is the one that contains the dates.

      1. Hi Irina,

        Thank for the support! This formula highlights days greater than 24 but includes past dates as well. I want this to work for current month only. I applied this formula "=AND(MONTH(Q9)=TODAY(),DAY(Q9)>24)" but it doesn't works. Can you suggest any revision?

        Thank you,

        1. Hi Irina,

          Thank you, I have figure it out. Here is the formula if anyone need.

          =AND(MONTH(Q9)=MONTH(TODAY()),DAY(Q9)>24)

          Thank you,

          Ali Khan

  19. Really very good job guys for post those valuable trick.

  20. Very good posts, You guys are helping almost every Managers from different orgs,..

  21. Hey, Irina. Its my pleasure for i have found this awesome blog site. I have learnt many tips & trick from here everyday. Thanks a lot for you & your teammates.

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