Comments on: Excel conditional formatting formulas based on another cell

This tutorial explains how to use Excel formulas to format cells and entire rows based on the values you specify or based on another cell's value, and provides a handful of formula examples. Continue reading →

Comments page 2. Total comments: 1074

  1. I'm trying to format the entire row if two conditions are true. I've gotten it to format the first cell of the row. Here is my formula- =AND(M13>=30, O13>=55) and it I have it applying to this selection- =$B$13:$O$13 (I'm working on one row for starters to make sure I get it right before I apply it to the entire table).
    What am I doing wrong? How do I get this to format the entire row?

  2. I have 5 columns, 2 rows.
    I would like to highlight the first cell (col 1-5) in row 1 that is >= the corresponding cell in row 2.
    once that condition is met (it could be col 3 for example), do not continue and check the rest of the columns (4-5).

    I tried the formalu =A$1>=A$2 applied to A$1:E$2

    thanks

  3. Hello :)!

    I have calendar (6 rows x 7 columns) and a list with assignments and dates. How can highlight day in calendar correspond to assignment date from list? I use =VLOOKUP(F6;$Q$7:$Q$37;1;FALSE)=F6 where F6 is day of calendar (6 rows x 7 columns) and range $Q$7:$Q$37 is monthly dates from assignments. Formula work and highlight all days in calendar (6 rows x 7 columns) bt not all days from assignments list have assignments. How I can add range $S$7:$S$37 where are assignments like criteria, something like =if($S7 "",VLOOKUP(F6;$Q$7:$Q$37;1;FALSE)=F6 in conditional formatting?

    Thank you in advance and have a great day!

  4. Hi,
    If C3 cell is having "R" means, i need "R" to appear automatically in J3 cell.

  5. Can you show me how conditional formatting yellow highlight for cells that have less than 2 units difference in value. Like 3,7,10, 9?

    1. Hi! Calculate the absolute difference of the values in the cells using the ABS function. Use 2 conditional formatting rules. Here is an example of conditional formatting formulas:
      For the range A1:A20:

      =ABS(A2-A1)<=2

      For the range A2:A20:

      =ABS(A2-A1)<=2

  6. Hello!
    I'm very new to writing out excel formulas and I currently have the rule set at
    "Format only cells that contain: Specific Text: Ending with: 9" to make them a specific color
    How can I create a similar formula to carry that color all the way across A-E instead of just column A?
    The value of Column A needs to determine what color the entire row is.

    Thank you so much!

  7. Hi! Great article but i´m having some trouble when I try to apply conditional formatting between 2 different values. Whenever I apply the AND formula you use, a pop up appears saying that there´s a problem with the formula and doesn't do anything. Do you know why this might be and how to solve it? Thanks!

    1. Hi! I will try to find the cause of the problem if you describe the problem in detail and write what formula you used.

      1. Hi! Thank you for answering. The formula I used was: =AND($Q22>2, $Q22<5) but whenever I try to use it, it tells me theres a problem with it. I have to use it as a new rule because it has to be applied to another cell.

  8. Hi, I'm wanting to highlight cells that have value smaller than the cell to its left. I selected the row and created the rule =c3<b3.
    This is my sample values I've been testing with:
    b3=70.6, c3=64.33, d3=64.3, e3=68.9, f3=74.3
    It's not highlighting c3 and d3. It's highlighting b3, c3 and f3 instead.
    Does it not work for decimal numbers or cells with percentage format? Do you have a solution for this?

    1. Hi! For the range $B$3:$F$3, use the conditional formatting formula =B3Cell references in Excel conditional formatting.

      1. Not sure what I'm doing wrong..still not getting the right results

  9. Hi there!
    I'm trying to use this for a schedule, where, when I type the abbreviation of a subject, say "mt" it formats the cell, including writing in the text "Maths". I've gotten as far as formatting the cells and that it fills in text but it always keeps the initial abbreviation that i used, so it gives me the formatted cell but the text is "mt Maths". this is due to the format code, where in the 'Number' field, i select custom (it tells you to start from a previously existing code) and i chose "@" and then my text in between the "" (so i type @"Maths"). I understand that the '@' copies and takes the text i wrote in the cell, but when i removed the @ sign from the code, it won't recognize the text written after, only when I add the @ sign. Can you help me with this? I'm trying to keep this as a formatting rule so I don't need a formatting rule AND a formula. Thank you!

    1. UPDATE!!!
      I found out, you have to put ;;;"desired text". Thanks anyways!!!

  10. I've just wasted 2 hours trying to get your examples to work - the problem is the way you have written the AND formula which looks like a space has been added in your table - and Excel is not having it! Only when I saw the image of the actual formula entered, without a space did it work. If you're going to provide help, please stop making it more frustrating than it already is by giving the incorrect syntax!

    The spacing you have used in the table makes it look like there is a space after the Function AND, when there is not.

    1. Hello Marc!
      You can see that there is no space after AND if you copy the AND formula from the article and paste it into an Excel cell.

  11. He Alexander,

    I'm trying to get a cell to change color based on a review comment on another sheet of the excel file.
    The cell that I'm trying to change color is the reviewer and based on his response it should color green (approve) or yellow (remarks added).
    I've created a formula that evaluates to "TRUE" in a cell on the worksheet but as soon as I paste it in the conditional formatting formula field Excel states it is not a formula, what is happening here?
    The formula is =XLOOKUP($D718&"*";DRF[Title];DRF[Rem-MF];"";2)="Approve"

      1. Thanks for the answer Alexander, I've changed the formula to =XLOOKUP($D4&"*";DRF!$A$2:$A$5000;DRF!S$2:S$5000;"";2)="Approve". This seems to work.
        I've included the column ranges to increase speed of calculating the formatting since using full column references slowed down Excel a lot.
        Funny that I've never come across the structured reference restriction for conditional formatting anywhere on the web while searching for a solution to this.

  12. I need help with figuring out if what I'm wanting to do is possible. I'm trying to update a spreadsheet a co-worker created. It has all employee names in one column and then other columns have department names, and other business releated stuff. Each column has employee names listed underneath. All the data is in black font but I went in and changed the font color in the main employee column so that each department has a different font color. Is there some type of conditional formatting formula that I can enter so that all the data in the other columns will update to match the font color in the main column. For example, if Susie Smith has blue font I want her name in all other columns to change from black font to blue font. This list has over 3000 names on it so I dont want to do one at a time.

    I hope I explained good enough so you know what I'm wanting to do.

    thanks for the help!

    Scarlett Jo

  13. Hi there,
    Love your articles as they are very helpful.
    Is there a way to create a conditional format whereby if a cell has a formula inside which includes a +1, then it colours it differently? I'd want to put this condition on specific columns for a pay sheet where if an employee works an evening shift and is paid an extra hour premium, once I enter the +1 in the formula of total hours, it automatically highlights that cell in a different colour.

    I.e. Formula in cell F205 is =(IF(D205>E205,E205+1,E205)-D205)*24
    result would be no highlight in cell F205
    but if cell F205 is =(IF(D205>E205,E205+1,E205)-D205)*24+1
    then cell F205 would highlight in green

    Any help would be appreciated

    1. Hello Margaret!
      Use the FORMULATEXT function to extract the formula text in the cell. Then determine if there is a partial match between the text string "+1" and the formula text. For more information, please read: How to find substring in Excel. Here is an example of a conditional formatting formula:

      =ISNUMBER(SEARCH("+1",FORMULATEXT(F1)))

  14. Hello,

    I love your articles, & find them very helpful! Here's my problem, using conditional formatting.

    I want a cell A to change color when cell B meets a certain value, (say 25), and cell C is between a certain range, (say 1-10).

    I understand the range part =AND($C$1>=1,$C$1<=10)
    I just can't seem to get my head around adding the cell B condition to the formula?

    Any help would be greatly appreciated!

  15. Hi, I want to know how I could use the conditional formatting for the range of cells, considering their total sums. 
    (Ex: total value of A column selected cells; total value considered with B, C, and D column selected cells.) If the A column selected cell total value is the lowest, then it should be highlighted with green (using red-yellow-green color scale formatting).

    1. Hi! I don't really understand from your description which cells you want to compare. However, you can determine the lowest value using the MIN function. Here's an example of a condition:

      =A1=MIN(A1,B1,C1,D1)

      If this is not what you wanted, please describe the problem in more detail.

      1. 1 A B C D
        2 MALA SEETHA RADHA RAMA
        3 500 200 150 162
        4 600 300 600 150
        5 700 20 inlcuded 170
        6 800 250 included 180
        7 900 50 850 200
        8 600 400 900 400
        9 200 60 200 750
        10
        11 4300 1280 2700 2012

        I need to use the conditional formatting considering the total value of each and every column selected cells.
        Ex: What is the range of A4, A5, A6 total compared to the total of B4, B5, B6, Total of C4, C5, C6 & Total of D4, D5, D6

        1. Instead of one cell, use the sum of three cells. Here is an example of the formula:

          =(A4+A5+A6)=MIN((A4+A5+A6),(B4+B5+B6),(C4+C5+C6))

  16. I have a checklist in Excel. There is a box/cell next to each item. At the end of the list, there is a "done" box/cell. I want to use conditional formatting so that if I place an "X" in the Done box/cell, it'll put an "X" in all of the boxes/cells in the list above. But anything manually keyed in an individual item's box/cell would override the "X" that would appear if the Done box/cell had the "X".

    I realize I could use a regular formula, not conditional formatting, to achieve this, but I don't want the formula showing in the blank box/cells. I want it embedded there through conditional formatting.

  17. Does anyone know how I can formulate the following - I'm trying to set up a training event calendar and have two sheets in the workbook. One with a calendar with dates along the top and each department underneath -
    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
    Mo Tu We Th Fr Sa Su Mo Tu We Th Fr Sa Su Mo Tu We Th Fr Sa Su Mo Tu We Th Fr Sa Su Mo Tu We
    Business op's
    Architects
    PMO
    Capital
    Property

    Id like to add conditional formatting along each department line colouring in the cell based on a date and the duration per each sub department. Id then like to formulate the coloured cells so when I click on them it takes you to the exact event in the event information tab -
    Description Event 1 Event 2 Event 3

    Capital Event Name Capital Group
    Number of days 5
    Time of Event 9am - 5pm

    Start Date 24 January 2024

    1. Hi! For information on how to format by date as condition, take a look at this article: Excel conditional formatting for dates & time: formulas and rules. If I understand the problem correctly, you will also find this article useful: Change the row color based on cell value.
      To move to another cell or worksheet after clicking a cell, you can use hyperlinks. See here for detailed instructions and examples: Hyperlink in Excel: how to create, edit and remove and 3 ways to insert a Hyperlink to another Excel sheet.
      I hope my advice will help you solve your task.

  18. I have 2 sheets (sheet1 and sheet2). I have set of words in both sheets same columns (B).

    I want to check words of each cell in sheet2 with range of words in sheet1 and change background colour of row whose word exists in sheet1's set of words.

    I tried using this lessons but I am unable to check value of each cells one by one.
    means that in conditional formating formula when I write this formula "=COUNTIF('sheet1'!B10:B45,'sheet2'!B$10)" then 'sheet2'!B$10 is remaining same and it is not changing to 'sheet2'!B$11 or B$12 as per cell.

    how can I do that?

    2. I have tried "sheet2'!B11" this also (relative value - means not using $ sign ) then also not getting desired output.

  19. if the below is typed in a cell
    80 x 80 x 3 x 6000
    can we get the product of these numbers in another cell . the answer has to be 115,200,000.

    is it possible

  20. We have a complex formula that produces post menstrual gestational ages into a cell.
    Example. Our formula provides a value 34.2 which is 34 weeks and 2 days. Our formula is also set to adjust this values every day so it’s up to date with the date change.

    When we follow instructions to a T, it doesn’t work right even tho our formula is correct. Is there a reason conditional formatting won’t work if it’s formatting a cell(s) that have a value that is produced from an already complex formula?

    1. Hi! I don't know what "instructions to a T" means. Conditional formatting works with the value that the formula returns. To give more precise advice, you have not given any information.

  21. Hi Alexander, Thanks for taking time to help those of us who are struggling with Con formatting BRAVO ZULU!

    My problem:-
    I have set of scattered cells, (that are percentages) in a column. I am looking to flag these cells red, if their sum is is not equal to 100%.
    say B18= 2%, B41=32%, B58=33% and B76=34%. The sum of these =100% so, no color change to b18/b41/b58/b76.
    If the sum is not equal to 100% change these 4 cells to red
    Thanks for your assistance.

      1. It works!!!
        Thanks so much, looks so simple when you set it out.
        I battled for hours and got nowhere- slowly.
        Thanks again

  22. I managed to conditional manage cells .. for example
    if value of cell A1 = >0 .. then cell A2 is colored red
    BUT what to do if I want to formate ..
    if value of cell A1 = >0 .. then cell A2 has the value of 1
    (used for automatically counting the cells that have a value >0)
    Is that possible?

    1. Hi! What you want to do is not formatting a cell. To set a value in a cell depending on the value in another cell you use the IF function. The following tutorial should help: IF function in Excel: formula examples for text, numbers, dates, blanks.

      =IF(A1>=0,1,"")

      To automatically count cells that have a value greater than zero, use the COUNTIF function. Read more: COUNTIF function in Excel - count if not blank, greater than, duplicate or unique. For example:

      =COUNTIF(A1:A10,">0")

  23. Hi Abelbits Team

    I'm attempting to create a formula rule that will change a cell fill colour if the 5 digit number entered in that cell is at a later date entered on another sheet in the workbook.

    Is this possible?

    1. Hi! This can be done by specifying in a conditional formatting formula the exact cell on the other sheet where this 5-digit number can be written. Or, write a formula in that cell that returns TRUE if the value is found on another sheet. For a more precise answer, your question contains no data.

  24. Hello,
    I have to highlight duplicates Values in Column A, If value in the column is "No". Also, duplicate value is equal to "Yes" I dont want to highlight that cell.
    ID Received
    12 No
    12 No
    13 Yes
    13 No

    1. Also i dont want to consider ID's for duplicate values if its equal to "Yes".

  25. Hello there!

    I have a budgeting template I'm using and I can't really get the conditional formatting to work.

    I have columns in a table labeled: Amount, Budget, and Difference. I'm trying to use conditional formatting on the Difference column with Icon Sets. Whenever I clicked on the Icon Sets button and selected the one I wanted, it would place the icon in the column, but it wouldn't be accurate for what I was wanting it to do.
    I'll put an example below:

    Amount Budget Difference
    $25.00 $50.00 (Up Arrow Icon) $25.00
    $75.00 $25.00 (Up Arrow Icon) ($50.00)
    $100.00 $20.00 (Right-facing Arrow Icon) ($80.00)

    That's just a small example of what keeps happening to me. I wanted to see how much more negative I was compared to the amount I budgeted for; however, the icons are misleading. It'd put a green/good arrow where the number was negative and should have had a red down arrow. I hope this makes sense. I'd include a snippet of my screen for more clarity, but it won't let me paste the image here. Please help!

  26. Hi,
    Thanks for your time.
    I am after a formula for conditional formatting that relies on data from 4 other cells.
    Basically, I want cell A4 to switch colour based off whether the values in B1, B2, B3 and B4 all equal "Pass". If they all don't equal pass, then the colour doesn't change.
    The conditions for B1:B4 equal: Pass, Fail or "blank"
    I'm thinking a formula something like: =(B1="pass"+B2="pass"+B3="pass"+B4="pass")

    Thanks for your assistance.

      1. Thanks Alexander, that worked great.

  27. I want a simple calculation, of one value divided by the value in the next column, to highlight the first column where the value exceed a limit, e.g. =$C2/$D2>3600, but it is highlighting cells that do not meet this condition and missing cells that do. I am baffled as to why this doesn't work - there are other conditional formatting rules on the worksheet, but they only apply to different columns. Do you have any advice as to what I could be missing please?

    1. Hi! Unfortunately, you don't write what errors you are getting. If you apply the conditional formatting rule to the whole column, do the formula for the first row =$C1/$D1>3600
      If this does not help, explain the problem in detail.

  28. =AND($L2>365,$M2<18)
    I am using this formula to get rows from L row which has greater than 365 and M row has less than 18. The M row contains % values. It highlighting this data - 5584 112.95.

  29. I haven't used Excel in years. I'm using Excel 360's Year Calendar template. Column A has numbers 1-31, columns B-M are months that contain text of when a project is due. I used Conditional Formatting to change the cells color to yellow when text is entered into a cell. I want to use Conditional Formatting to change the cell color to orange when the due date is 14 days from today and red when 7 days from today. Can you help?

    I've used =$A3=TODAY()+7 nothing happens

    Thank you very much for your time

  30. I am trying to conditionally format column A or based on a values in column B. However, it does not do anything.

    Contact Information (A) Key (B)
    Contact Name 100
    Nickname / Preferred Name 2
    Relationship Name 100
    Title 72
    Department 5

    Rule
    =$B$2<20 applied to =$A$2:$A$6 (Trying to Conditionally highlight Column A values when its column B value is less than 20)

    The rule works when individually applied on each row. Example $B$3<20 applied to $A$3 works.

  31. Hi this has been really helpful but I am trying to do a conditional formatting formula - I have a cell with a date when a complaint is received and have a cell where the response date is put
    I want to highlight the response date cell amber if it is 7 days or more from the received date and highlight the response date cell red if it is 15 days from the received date to show when responses are due, can you help at all? thank you

      1. many thanks

  32. Hello, I'm trying to format a pivot table that says if $B15="(blank)" then format the font on that entire row 'white'. This pivot table will be using different size data sources, so how do I need to use the applies to section. Every time I enter a range it automatically changes to absolute values.

    1. Hi, what formula must i use if i want to carry over a value/data from once cell to another if that value is greater than an zero. I want to say: =A2, if the value in A2 is greater than 0, otherwise 0

  33. Hi, I'm trying to set up a sheet to compare pricing from 3 different vendors. I have over 400 rows and I want the cheapest one in each row to be highlighted. I set up a formula to look like this. =AB6=MIN($AB:$AD6) and I applied to all the rows. It went and highlighted each row, the problem is, in some rows it did not highlight the cheapest one and in some rows it highlighted more than one cell. Can you help me?

      1. Hi, I commented earlier. The formula you provided is the formula that I used, and did not work. Is there a way to fix it?

        1. If you look carefully, I suggested a different formula. MIN($AB:$AD6) - it's wrong and it's impossible. If you apply it to the entire range, e.g. A1:C4000, the minimum number in each row will be highlighted. I may have guessed wrong, as I am having trouble understanding your question: "I want the cheapest one in each row to be highlighted".

  34. In one sheet there are ITEM#, QTY, PRICE, CAT#. In another sheet there are ITEM# & CAT# which is populated. I have to enter the ITEM# in the first sheet and using vlookup CAT# will be fetched from the 2nd sheet. Now I want to apply conditional formatting in the 1st sheet on ITEM# column so that if anything outside the range of ITEM# entered from 2nd sheet then the cell will be colored in sheet 1 on ITEM# cell. Please advise what formula should I apply in the conditional formatting here.

  35. Good day,

    I tried to apply your tutorial to my situation, but my brain is not connecting the dots. I want to format a cell in column H if cell D is "Yes" and the time in H is > 00:01:20. See my summary with the or condition below. I'm getting errors on the variations that I'm attempting.

    Format cell:
    If D2 is Yes, and H2 > 00:01:20
    or
    D2 is No, and H2 > 00:05:00

    I assume two different conditions with stop if true, but I can't even get one to work.

    Thanks for any guidance you can provide.

  36. I work a lot with conditional formatting, sometime up to 100's at a time.
    Since the excel is more and more used in the cloud, and VBA is not possible anymore, I stumble across this, which maybe, very maybe, can be resolved by conditional formatting:

    I change a value in a matrix, let's keep it simple $A$2:$E$2
    By a formula I can change the date in $F$2 to today

    Is it possible to do the following with conditional formatting (it is NOT possible with a formula):
    I want $G$3 to display the date of the last change in the matrix.

    Since everything is always updated, I think it must be impossible however, there is a menu 'review/show latest changes', is there way to access those values, then we are saved.

    thanks for any response
    Johan

  37. This is all beyond me however, I am trying to learn. I want to change the colour of the font in a cell when 2 other cells have text in the cell. i.e. B2=when C2 and D2 have text. Is this something that is possible? Any help would be greatly received.

  38. Hi,

    Fascinating examples and too complicated for me. I have only used conditional formatting for finding duplicates in different columns. It works fine for most item but some items that appear to be identical are not marked in red. My case is addresses. I am very carful about spacing, spelling, and punctuation all of which if not identical will fail to fail two items as identical. Any idea why Excel refuses to recognize visually identical items in isolated instances? After copying one oveer the other they do turn red (obviously). This is frustrating in trying to elimiate duplicate addresses. Any? idea what is going on.

    Charles

  39. I am trying to replicate a conditional format. For each new cell the conditional value changes. So for example the result in A2 is /= to A1 but the result in B2 is /= B1. So far I can only get the results for A2-F2 to be conditional on A1 and not change for each new cell. I hope I'm explaining this right! Apart from just doing over 100 cells individually is there a way to easily replicate. Thanks

  40. I am trying to highlight duplicated if column A and column B are the same. I am creating a spreadsheet with book and page Column A is the Book and Column B is the page I want the duplicates of the same book and page. So if book 135 and page 2 repeats I want that highlighted. Is there a way to do this?

  41. Hi, I am trying to highlight a cell if the value of one cell id not equal to AND the value of a cell is equal to.

    I tried this but is not working

    =IF(AND($C44"#N/A",$D44="#N/A"),TRUE,FALSE)

  42. Hi, I have a list of dates for deliverables and want to highlight those that are due today; due this week; due next week; etc. I used the 'Date Occurring' prebuilt criteria which is great for highlighting the date (in column C), BUT I want to also highlight the task description (in column A) the same colour as the date. I can use the formula, but I cannot see a formula that has the concept of "This week" (I can do 'between TODAY() and TODAY()+7' but this is not the same concept as 'This week' which is the Sun-Sat week (or Mon-Sun).

    Do you have any suggestions? Otherwise I will just have to rebuild it using the 'between' formula which doesn't quite do what I wanted (with people being able to see those tasks due this working week, next working week, etc. and would instead be due in the next 7days, or the next 7-14 days.

    Any suggestions? Thanks :)

    1. I have solved it, though there may be a more efficient way.

      I ended up using the "WEEKNUM" function. So, if the due date is in cell C1, then:

      This week is WEEKNUM(TODAY())=WEEKNUM($C1)
      Next week is WEEKNUM(TODAY()+1=WEEKNUM($C1)

      If there is a better way, I'd be happy to hear it. As it is, this is my IF statement for the due date descriptor column:
      =IF(ISNUMBER($C1),IF($C1TODAY(),$C1TODAY(),$C1<TODAY()+31),"This month",""))))))),"")

      This may be a bit overdone with too many options so may be refined (removing the next fortnight for example), but for now it's what I was after.

      Happy to hear any feedback/criticism/ideas!

      Keep up the great work :)

      1. It cut off my IF statement as it doesn't like some of the syntax. Not sure how to get around that. Needless to say, that formula is not correct! :)

  43. I am trying to figure out how to use conditional formatting so that if I have a group of five cells the fifth cell is red unless there is text in any of those five cells. So I would like A5 to be the color red but if there is text in any cell A1:A5 then the color of the A5 cell would be gray. Is this possible?

  44. I have been struggling with conditional formatting for weeks trying to crack this code! I have a spreadsheet that is tracking Personnel (Column A), with a completion date (Column C) within a time frame of 6 months. I have set up 3 cells with current date (E44), start date (E47), due date (E48) separate from the table.
    I need a formula that will highlight cells in column A, based on whether Column C is blank AND a specific Cell (E44) is within 60/30/15 days of the due date listed in Cell (E48). Each increment will be formatted to a different color (i.e.60=blue, 30=yellow, 15=red).

    1. Hi! If I understand your task correctly, the following conditional formatting formula should work for you:

      =AND(ISBLANK(C1),$E$48-$E$44<60)

      Combine the conditions using the AND function.

  45. Hi there! I'm having quite a bit of difficulty with conditional formatting. I've been trying to figure this out for weeks now! I would appreciate any amount of help!

    Here's an example of what I'm working with:

    Column A has dates and Column B has rooms. I want a duplicate value in Column B to be highlighted only if there is also a duplicate date in Column A of the same rows. Basically, if Room 1 is being listed twice on the one date, I'd like it to be highlighted.

    For example: I would like to have a formula that formats B2 and B3 if A2 and A3 are the same, but not include B4, because the room is different, and not B5 because the date is different.

    A2 B2
    01 January Room 1 - Highlight
    A3 B3
    01 January Room 1 - Highlight
    A4 B4
    01 January Room 2
    A5 B5
    02 January Room 1

  46. JA51______________________JD51___________JE51___________________JF51________________JG51
    *Jul - Overtime __________9:00____________R4 277.37_____________9:00________________R4 277.34

    Sorry, I am resending my request to see if me worksheet extract will be better understood.

    I want to highlight cells JA51:JG51 green when JE51 is within 5% of JG51. In row 3, I have numbered the columns for use in another formula.

    I have tried the conditional formatting formula below. When I split the two AND functions, they both work, bur when I combine them into the AND function, I get no highlight.

    =AND(INDIRECT(ADDRESS(ROW(),JA$3+3,3))>=(INDIRECT(ADDRESS(ROW(),JA$3+5,3))*0.95),INDIRECT(ADDRESS(ROW(),JA$3+3,3))<=(INDIRECT(ADDRESS(ROW(),JA$3+5,3))*1.05))

    Many Thanks

  47. Hello Svetlana Cheusheva,
    I am doing a very simple =A1'OLD'!A1. I am applying it to cells A1:CH3000

    I have a column CC that has mixed formatted data (some cells are numbers others are text) that is outputted from Access. It is coming out as "General" format.

    I have verified that BOTH New tab and Old Tab have same formatting for this column, yet EVERY cell in that column that has data is highlighted.

    If I go in to the cell, CC1 and check for spaces... the formatting resets and goes... hey I match CC1 on Old now.

    This is a huge doc and I'm trying to highlight changes from the previous week's report.

    2. =A1 'OLD'!A1
      The comment box keeps wiping out the It should read A1 is not equal to OLD A1

    3. =A1'OLD'!A1

  48. Hello.
    I created a conditional formatting rule to make text within a cell bold and red if it equals 'NEIN'.
    The rule does not work consistently. Every once in a while I have to manually change the color.

    It's not really a big deal, just a little frustrating.

    Is it possible to use a formula to automatically format text in a cell? I'm using Excel 2021 on Windows 10.

    Thanks in advance.

  49. Hello~

    I've tried conditional formatting and it doesn't apply formula.

    Example :

    I want to change the value of a cell to "0", if another cell has the word "cancelled" in it.

    The formula I've written:

    =if(D3="cancelled", "0", false)

    For some reason it doesn't apply to the cell or row.

    Thank you in advance~

    1. Hello! Conditional formatting cannot change the value of a cell. You need an Excel formula for that. You have written this formula. You can also change the value of a cell by condition using a VBA macro.

  50. I want to be able to dynamically color a range of cells based on another range of cells. From what I'm seeing here you would have to manually write a rule and manually program a format for every single possible color. This is an unacceptable solution. It should be easy to simply have a range from 1-10, and color your cells based on whichever value from 1-10 you have; you should not need to manually program all 10 rules.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)