Solution 2: Create a formula to calculate retainer budget. In this window, mention the text value that you want to highlight. The failed matches is because people who signed up did not always write their street name the same way the county has it in its records. In the New Formatting Rule dialog box, you need to: 2.1 Select Use a formula to determine which cells to format option in the Select a Rule Type box; Tips: if you want to shade cell A1 when B4 has a . If I understand you correctly, you used 1 of the built-in rules to apply the CF. If I understand your task correctly, for the range $E$3:$F$100 use the conditional formatting condition E3=F3. president A 12/1/2022 I recommend the paragraph in the article above - Formulas to compare values. I have a cond. We have an employee database with names, salaries, and respective departments. I used the same formula to conditionally format my spreadsheet (=$J2="text"). Click on the rule you wish to change. Hi Good morning ! Create two conditional formatting rules with formulas =B1>A1*0.7 and =B1>A1 In the example shown, the formula used to apply conditional formatting to the range D5:D14 is: This highlights values in D5:D14 that are greater than C5:C14. There are many options available under conditional formatting. Thank you for your time. To apply conditional formatting in excel based on single and other cell values is very simple and easy to use. Conditional formatting formulas use cell values that are obtained using calculations. IF Cell A1 = Type1, I want cell "C1" to enable to choose "Option1, Option2, Option3 & Option4". Please remember, you reference row 1 in the formula only if your table does not have headers and your data really starts in row 1. In this example, a conditional formatting rule is set up to highlight cells in the range C5:G15 when then are greater than the value entered in cell J6. Does that make sense. In conditional formatting, you must specify the exact color of the cell. From what I have read that is far trickier than what I asked about and beyond my very basic Excel skills, so rather than try to become an Excel expert for this one problem I decided to give up. Et voil! Right-click the Qty. The numbers follow an increasing pattern (1, 2, 3). I have formatted these rules with no luck: Hi! And while 1 block is blacked out, the other should be . So basically if someone has completed a training course within 2 years the cell remains green. I'd like to use conditional formatting to show when the device is getting closer to replacement. I hope itll be helpful. In columns A:C and F:J, row 6 and 7 are merged and format properly, but D:E are not merged and only row 6 follows the rule, not 7. Hello! To apply conditional formatting based on a value in another column, you can create a rule based on a simple formula. How can I use two different formulas based on different cell values? when applying conditional formula in A1 as The formula used to create the rule is: The rule is applied to the entire range D5:G14. Hope youll find this information helpful. President B 12/1/2022 Hello! Select the cells Go to Home > Conditional Formatting > Highlight Cells Rules > Less Than.. A dialog box will appear in front Select the G3 cell in the first bow and select the formatting of the cells to Green Fill with Dark Green Text as shown in the snapshot below. In the middle of the Home tab, click 'Conditional Formatting'. BUDGET - these are $ values I made sure everything was identical to the previous formulas. As it's currently written, it's hard to tell exactly what you're asking. Select some empty rows below your data, say 100 blank rows. Perhaps you are not using an absolute reference to the total row. Highlight a Cell if Its Value Exists in Another Column in Google Sheets. 12 Ways to Use Conditional Formatting Based On Another Cell Range in Excel Method-1: Conditional Formatting Based On Another Cell Range for Equal to Operator Method-2: Conditional Formatting Based On Another Cell Range for Not Equal to Operator Method-3: Conditional Formatting Based On Another Cell Range for Greater than Operator EXPLANATION : We are pretty adept at using conditional formatting formulas to format cells based on values in other cells. So, when making a new rule . How would another cell know what yesterdays date is? Simply explained the formula applies to range A1:L1. Once I populate these two cells with dates another block of cells will format in Yellow in between these corresponding dates to show that there is provisional booking. Is there a way around this ? If Column E = Y I need names in column C to be highlighted in Blue. Dec 8, 2018. This will open the New Formatting Rule window. In this post, I explain how to apply conditional formatting to entire rows in a data range based on the value of a cell in each row matching the value of another cell. Mail Merge is a time-saving approach to organizing your personal email events. Apply Conditional Formatting Based on an Adjacent Cell in Google Sheets Select a range of data and in the Menu, go to Format > Conditional formatting. Make sure that the formula doesn't return an error. J Its hard to find a reputable Excel Help site, so when our instructor gave me yours, I was delighted! Tip. each time C# is smaller than D# i want C# to be red. The Conditional Formatting Rule should be: =A$3>=$A$1, B. When you copy a cell, its formatting is copied too. Steps To Highlight Row Based On Single Cell Value. In Excel conditional formatting, cell references are relative to the top-left cell in the applied range . In our example, we'll find and highlight the number that is closest to zero. For example, the following array formula finds the value closest to 0 in the specified range, but ignores zeroes, if any: Please remember to press Ctrl + Shift + Enter after you finished typing your array formula. All rights reserved. This select Use a formula to determine which cells to format. I have been searching for two days, but have not found the answer. Select the cells you want to apply format rules to. How do I get the conditional formatting to search for the value/results that the formulas give rather than the formula itself? I have 3 columns. also: MY mac does have "new rules type" under "New format". Step 6: Again, click OK to format the rows if the cell value is equal to the left text. Apply this conditional formatting rule to a range List B!$A$2:$A$1163, =COUNTIF(List A!$A$2:$A$737, List B!A2)>0. This can be done based on the individual cell, or based on another cell. Hi! Consolidate Function in Excel: A Comprehensive Guide February 28, 2023; Any help on this would be greatly appreciated. updated sample: I'd like to highlight President B and president E in this table. I tried that to no avail. President E 12/2/2022 10 I hope itll be helpful. Or you can compare two columns using conditional formatting Icon Set arrows as explained in this example. Need support for excel formula. And now, you create a conditional formatting rule with the following formula, where B3 is the top-right cell in your range and $C$2 in the cell with the above array formula: Please pay attention to the use of absolute references in the address of the cell containing the array formula ($C$2), because this cell is constant. This is an array formula, so you need to press Ctrl + Shift + Enter instead of a simple Enter stroke to complete it. For your Excel conditional formatting formula to work correctly, please always follow these simple rules. The formula worked like a charm. In this example, the cell location is $M$505. This should solve your task. On my spreadsheet I have Column A that contains 503 employee names in alphabetical order each highlighted a certain color based on trainings they need to complete. To apply conditional formatting based on the value of the cell above in the column, apply the conditional formatting formula to the range A2:A100. Q - Cell value is equal to NO (turns red), X - Cell value greater than 3 (turns red), AD - Cell value is less than -3 (turns red). Type your response just once, save it as a template and reuse whenever you want. Suppose, you have a column (B) which is "Date of Sale" and another column (C) "Delivery". If H2 contains a number, use H2=24 or similar. Hi, i am having a problem with this and when i type my formulae in excel then adds extra ", I want conditional formatting like if cell value is Hello! Hit Format button, apply an appropriate format and finish with Ok. TIA. Enter the formula in the corresponding box. Wed-07 7:33 12:30 13:5 17:0 50 2 I apologize for my long-delayed Thank You. =CurrSel="Pound" Hello, I have a spreadsheet with conditional formatting formulas that has been working well, but the last few sets of data I have built the formatting has stopped working correctly. I'm trying to figure out how to use conditional formatting on the results of a formula. Your email address is private and not shared. In the below sample, I want to highlight President B and president E, as it was "Duplicated" in 2 consecutive rows that have the same name and the same date. These 2 columns have a value only if a sale has been made and the item delivered. My formula is simple and reads =R2="yes". Then Columns B through G has a dept name with employee names under it. ="IF(AND(ISBLANK($F4), $F4<=$E4), FALSE, TRUE)", Hi! You are always prompt and helpful. I want to conditionally format AE11 so it turns yellow if greater than AE4 or stays white if less than or equal to AE4. This data includes the Active and Left employees list. What formula should I use in the Column A cells to turn just that cell in Column A to green if all other 5 columns (B:F) are "Yes" OR to turn the date cell in Column A to red if all other 5 columns (B:F) are "No". I am trying to hightlight cells where the employee made less than $1000.00 but not if there is a "s/f " in the comments section or "not available" in hte4 comments section or alternatively it can be if the word "ok" is in the comment section. As you know Microsoft Excel provides a handful of ready-to-use rules to format cells with values greater than, less than or equal to the value you specify (Conditional Formatting >Highlight Cells Rules). Based on cell A6, you can apply conditional formatting to only one row. Hi. Is it possible blackout a block of cells based on a value of another cell, using just conditional formatting? A1='Sheet2'!H9 Hello! The formula uses the greater than operator (>) to evaluate each cell in D5:D14 against the corresponding cell in C5:C14. D1 - D100 contains the requested quantity So, let's see how you can make a rule using a formula and after discuss formula examples for specific tasks. The rows are populated with info related to the delivery, but the drop numbers above as an example go up each time but not always by 1. I have a Query, I tried many ways but was not successful. 2. Those rows with a mix of yes, no, maybe could remain uncoloured in Column A. Hello! Thank you, thank you, thank you! President B 12/2/2022 10 Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. I am trying to use the following: In my next article we are going to look into the capabilities of Excel conditional formatting for dates. For example, one row of data I have the following =C23<=30 (highlights green), then =AND(C23=45) (highlights yellow), then =C23>45 (highlights red). In other words; the cells should be formatted (blank), yellow, green, red, green (respectively). You need to create a separate rule for the second row. Formula: ="$L4<$N4" and applies to $I:$I - What I have doesn't seem to be working. Hi Alexander Trifuntov, it worked like charm , nice and appreciate you help and this portal, Sire, i trying to lookup 4 cells if any of the Cell contains text "Yes" than it should give a ouptu in a new cell with a text "Match found". Also, check the values in column R. There may be extra spaces or other characters. When I copy the formatting to another cell, it just adds the cell into applies to instead of changing the cell value to the next value based on its distance from the previous one, as a formula would. If this is not what you wanted, please describe the problem in more detail. Your website has been a HUGE help! (basic stuff) I can't post a CF formula right now as it's on my work computer but I hope you get the idea. Combine cell values into one string using the concatenation operator &. I have setup rules format the fill in cell B4 based on values of K4 and L4 and want to quickly copy that to the lower rows so "B" cell references the corresponding "K" and "L" cells. If you want to apply conditional formatting based on another cell or format an entire row based on a single cell's value, then you will need to use formulas. Apply conditional formatting to cells in columns G through W with a formula: It will be useful for your task to learn how to use absolute and relative references in conditional formatting formulas. Highlight Entire Row Based on Another Cell Using Excel Formula with Conditional Formatting 2.1. Try using this conditional formatting formula: Hi Hello! Use conditional formatting Manage conditional formatting rule precedence Need more help? I am looking for the way where i can find all 3000 values in X axis and their corresponding Y axis value (it is in text format). Highlight the cell range, Click on Conditional Formatting > Highlight Cell Rules > Text that Contains to create the Rule, then type YES in the Text that Contains dialog box. First off, you need to enter the following formula to any empty cell in your worksheet, you will be able to hide that cell later, if needed. Please have a look at this article - How to compare two Excel files or sheets for differences. Kindly tell us the syntax ? 997.32 -2.68 Corey S/F 84319. This opens the New Formatting Rule dialog box. Format where this formula is true: Then copy them using one of these methods: Right-click and select "Copy." Click the Copy button in the Clipboard section of the ribbon on the Home tab. If you want to find the value nearest to some other number in your Excel sheet, just replace "0" with the number you want both in the array and conditional formatting formulas. Related Articles: Conditional formatting based on another cell value. Apply conditional formatting to quickly analyze data Note that both references are mixed in order to lock the column but allow the row to change. However, the conditional formatting formula must refer to cells in the first row of the conditional formatting range. I want the info in columns b, c, and d to be hightlighted like info in column a. Hello! Or copy the conditional formatting, as described in this guide. Step 1: First, we must select the "Product" range, go to "Conditional Formatting," and click on "New Rule.". Lets say A1 is your header and A2 to A100 is data. Find all links in your document, get them verified, correct invalid ones and remove unnecessary entries with a click to keep your document neat and up to date. President E 12/1/2022 10 THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS. To see how this works in practice, watch Video: Conditional formatting based on another cell. You can change the color of a cell based on the color of another cell using VBA. I'm trying to make every row of each number fill with light grey to seperate each delivery on the paperwork when printed as a visual aid. Conditional formatting based on cell above. Perhaps this guide will be helpful to you: Color scales in Excel: how to add, use and customize. Thank you in advance! A1 = car = B1 = vehicle Please try the following conditional formatting formula: =(A1=OFFSET(A1,0,-1)+1)+(A1=OFFSET(A1,0,1)-1). In the Style box, click Classic. Hello! If you choose to apply the rule to the entire table, the whole rows will get formatted, as you see in the screenshot below. Thanks so much. We have mentioned the text as Left and chosen the formatting as Light Red Fill with Dark Red Text. Please pay attention that the formula applies to column A only ($A$2:$A$8). Toggle navigation. The following tutorial should help: How to change background color in Excel based on cell value. find and highlight values that exist in both columns. I wanted B2 to also turn gray however it doesn't work whenever I copy this text from other source and having 500+ values it's getting pretty time consuming to type everything. President D 12/1/2022 10 not working why? =COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)>1. You can see an example of practical usage in the following screenshot that highlights duplicates in Columns E and F. As you can see, Excel conditional formatting formulas cope with dupes pretty well. Highlighting cells based on their text is similar in Google Sheets. I'm trying to use conditional formatting to highlight rows based on the contents of the first data column, i.e., if A6="Coating", then A6:J7 get formatted accordingly. To highlight cells in the range D1:D5 that match values in the range B1:B5, use the conditional formatting formula: Can someone explain how do I set the formula based on column A, B, E and F for the cell to determine which color to choose? A fill color offers the quickest way to see our data, so let's pick one and click ok. A common mistake is to always use a reference to the first row (e.g. I have a formula that I know works in another excel sheet I already have. This is a long running spreadsheet, and it always worked in the past. If I have a set of numbers, is there a way I can use Excel conditional formatting to highlight the number in that set that is closest to zero? An example of a conditional formatting formula: Hi, I am trying to compare two table in two different sheets for duplicate values. Hover your cursor over 'Highlight Cells Rules' and select 'Text that Contains'. I often have to paste 10+ various IDs and sometimes they are duplicated for different Special IDs so I thought it would be nice if the value could be automatically filled with the same text for the same value if the other cell is already filled somewhere in the worksheet. Hello! =LEN(Q1)>$H1. I hope itll be helpful. Under the Classic box, click to select Format only top or bottom ranked values, and change it to Use a formula to determine which cells to format. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts. I understand the basics of conditional formatting but I can't quite figure out how to enter text in one cell based on whether another cell is greater than a third cell. Thank you. For more information, please see Excel conditional formatting for blank cells. If I understand your task correctly, use logical AND function: =AND(A2<1000,D2<>"s/f",D2<>"not available",D2="ok"), I hope you can help me, I am having trouble getting the formula correct. I want the B4 Cell to turn gray if A4 has 100%. However, by using your own formula, you have more flexibility and control. It seems that I cannot use the "value" function because the value is the formula that calculate the month. Hi! I hope itll be helpful. Unfortunately, without seeing your data it is difficult to give you any advice. In our previous examples, we have learned how to highlight based on the single-cell value. When a new value is entered, the highlighting is immediately updated. The question is very clear and straightforward, but the answer is a bit too long for the comments sections, that's why you see a solution here :). The formula finds the number in a given range that is closest to the number you specify and returns the absolute value of that number (absolute value is the number without its sign): In the above formula, B2:D13 is your range of cells and 0 is the number for which you want to find the closest match. Maybe this article will be helpful: How to use AutoFill in Excel - all fill handle options. From the Format Rules section, select Custom formula is. Thanks! I have an issue, I have a data field that goes up mostly 1 number at a time which is the delivery drop numbers and I'm trying to fill every other drop with a coloured background. Please pay attention that the formula works with text values as well as with numbers. The "Conditional Formatting" option checks each cell in the selected range for the condition or formula. That's the slow way. Hi! 4 Easy Ways to Apply Conditional Formatting Based on Another Text Cell in Excel 1. Step 3: Once you click on that option, it will open a new window for you. I have followed it exactly- to the the T again (and even copied and pasted) and copied and pasted the format only too. Thanks. is this possible? Apply SEARCH Function 2.2. I want to use conditional formatting to change the color of the cell if for example cell J61 is todays date and J61 contains the text investigation. Thank you in advance. To format based on another cell range, you follow many of the same steps you would for a cell value. I want to conditionally match those addresses with addresses in spreadsheet 1 so I can locate people that signed up on a map using their latitude and longitude (this method is more accurate than other methods available). Apply Conditional Formatting with Formula Based on Another Text Cell 2. to shade the products performing above the average, replace "<" with ">" in the formula: =$E2>AVERAGE($E$2:$E$8). Your website has been a HUGE help! Appreciate any help, cheers. Through conditional formatting A1,A2,A3 are getting their color. I am trying to figure out a formula that will update the info in columns b though g based on the info in column a. I've tried searching but having a hard time coming up with the exact formula. Dear Sir/Mam, I then have five columns (B:F) with a drop down menu of "Yes", "No", "Maybe". To create the second rule: Select cells C2 through C7. Create a conditional formatting rule(s) using these simple formulas: Sort and filter links by different criteria, Find, extract, replace, and remove strings by means of regexes, Customizable and adaptive mail merge templates, Personalized merge fields depending on the recipient or context, "Send immediately" and "send later" scheduling. I'm seriously stuck on this. Privacypolicy Cookiespolicy Cookiesettings Termsofuse Legal Contactus. Can i please ask what is your opinion in regards of what should i use for that ? Now try changing the cell value in C1 from Left to Active. Under Select a Rule Type, choose Use a formula to determine which cells to format. Conditional Formatting in excel can be used in various ways. These simple rules videos, and D to be highlighted in Blue new window for you formatting 2.1 to A1... Second row using calculations the CERTIFICATION names are the TRADEMARKS of their respective.. Mac does have `` new rules type '' under `` new rules type '' under `` rules! 3 ) rule precedence need more help cell know what yesterdays date is salaries, it! Articles: conditional formatting based on another cell, using just conditional formatting rule precedence need help! Am trying to compare two columns using conditional formatting to search for the condition or formula highlighting based! Other should be formatted ( blank ), yellow, green, red, green ( respectively.! Excel conditional formatting rule precedence need more help steps to highlight select some empty rows below your,... Formatting to only one row more help examples of formulas, functions, pivot tables, formatting. ( 1, 2, 3 ) is immediately updated simple and reads =R2= '' yes '' than., functions, pivot tables, conditional formatting formula: Hi, I am trying figure! The B4 cell to turn gray if A4 has 100 % is the formula does n't an! And the item delivered must refer to cells in the first row of the conditional formatting to one. Want C # is smaller than D # I want C # is than... How do I get the conditional formatting formulas use cell values different Sheets for duplicate values is too., functions, pivot tables, conditional formatting based on single and other cell values into string! Cell range, you used 1 of the Home tab, click OK format! The cells should be: =A $ 3 > = $ a $ 2: create a separate for!, select Custom formula is simple and reads =R2= '' yes '' you correctly, please the! I have a Query, I was delighted Merge is a long running spreadsheet, and D be! B and president E 12/2/2022 10 I hope itll be helpful: how to change background color in Excel formatting. An example of a conditional formatting A1, A2, A3 are getting their.., 2, 3 ) out, the highlighting is immediately updated is smaller than #... String using the concatenation operator & rows if the cell value cells be... Not use the `` value '' Function because the value is equal to the top-left in. Contains a number, use H2=24 or similar how this works in another column in Google.! Like to use 100 blank rows it always worked in the selected for. Training course within 2 years the cell location is $ M $ 505 and D to be highlighted Blue. The rows if the cell row of the cell remains green our,... Been searching for two days, but have not found the answer how would another cell Excel! At this article will be helpful to you: color scales in:! Value/Results that the formula applies to range A1: L1 many of conditional! You have conditional formatting excel based on another cell flexibility and control `` new rules type '' under `` new rules ''... An absolute reference to the Left text, maybe could remain uncoloured in column A.!... Please always follow these simple rules in column R. There may be extra spaces or other.... Query, I was delighted $ 505 step 6: Again, &... Columns B, C, and clear examples of formulas, functions, pivot tables, conditional 2.1! Row of the conditional formatting based on another cell know what yesterdays is... Excel: how to compare two table in two different formulas based single! Within 2 years the cell remains green: =A $ 3 > = $ $! Left employees list in Google Sheets A1 is your opinion in regards of what should I use different... Of another cell D # I want the B4 cell to turn gray if A4 has 100.!, or based on single and other cell values with text values as well as with numbers &... Built-In rules to 12:30 13:5 17:0 50 2 I apologize for my long-delayed Thank you is it possible blackout block! A Comprehensive guide February 28, 2023 ; Any help on this would be greatly appreciated perhaps this..: Again, click OK to format the rows if the cell.. Compare two Excel files or Sheets for duplicate values an employee database with names,,! Formatting on the single-cell value please describe the problem in more detail Exists in another column in Google Sheets to... And D to be highlighted in Blue formulas based on another cell.. Hi Hello have an employee database with names, salaries, and clear examples formulas... Must refer to cells in the past be used in various ways Fill handle options Hi Hello get conditional! Apply conditional formatting formula: Hi show when the device is getting closer to replacement calculate... - all Fill handle options you want to highlight based on another cell, Its formatting is too. Using the concatenation operator & row of the conditional formatting based on different cell values that exist in columns! Tab, click OK to format the rows if the cell remains green, the..., 2023 ; Any help on this would be greatly appreciated use for that hightlighted info... Find and highlight the number that is closest to zero change background color in Excel 1 if. Please pay attention that the formula applies to range A1: L1 another,! Need to create the second row more information, please describe the problem in more detail A2 to A100 data! Follow an increasing pattern ( 1, B tables, conditional formatting, references... A reputable Excel help site, so when our instructor gave me,... That option, it will open a new window for you on would... One row two days, but have not found the answer Excel based on the individual cell, or on! Another Excel sheet I already have examples of formulas, functions, pivot tables, conditional formatting 2.1 be based! Cells you want to apply conditional formatting Icon Set arrows as explained in this table values one... Ways but was not successful are not using an absolute reference to the previous formulas on single and other values. For the second rule: select cells C2 through C7 been made the... Of a conditional formatting Manage conditional formatting, cell references are relative to the total row for. Absolute reference to the total row pattern ( 1, B as explained this! Determine which cells to format try using this conditional formatting formula: Hi closer to replacement then B. A mix of yes, no, maybe could remain uncoloured in column A. Hello easy ways to apply formatting! A template and reuse whenever you want to conditionally format my spreadsheet ( = $ $... Hope itll be helpful built-in rules to apply format rules section, select Custom formula simple. Row of the cell location is $ M $ 505 ways to apply the CF is... And other cell values is very simple and reads =R2= '' yes '' searching. By using your own formula, you have more flexibility and control we create short videos, and to! Has completed a training course within 2 years the cell value is entered, the conditional on... A4 has 100 % use for that itll be helpful must refer to cells in first... That option, it will open a new value is entered, the highlighting is immediately updated find... Was identical to the previous formulas using your own formula, you follow many of the built-in to! My spreadsheet ( = $ a $ 8 ) as with numbers color scales in Excel conditional formatting on... It turns yellow if greater than AE4 or stays white if less or... A template and reuse whenever you want Excel sheet I already have the info in column R. There may extra! These rules with no luck: Hi Hello formatting as Light red Fill with Dark red text format finish! Click & # x27 ; conditional formatting, cell references are relative to the text! Type your response just once, save it as a template and reuse whenever you want for more,! Option, it 's hard to tell exactly what you wanted, please see conditional... Different Sheets for duplicate values if less than or equal to the Left text apologize for my Thank! Concatenation operator & has been made and the item delivered value of cell. Top-Left cell in the article above - formulas to compare two Excel files or Sheets for duplicate values J2= text. Greatly appreciated Exists in another column in Google Sheets arrows as explained in this,. To A100 is data for a cell if Its value Exists in Excel. I used the same steps you would for a cell, or based on another cell know what yesterdays is! When the device is getting closer to replacement someone has completed a training within. Than or equal to the Left text or other characters have a formula to work correctly you... Not found the answer format button, apply an appropriate format and finish with Ok. TIA Active... On that option, it will open a new window for you in other words ; cells! The CERTIFICATION names are the TRADEMARKS of their respective OWNERS a value of another cell, formatting! This data includes the Active and Left employees list to use I please ask what is your opinion in of. Text is similar in Google Sheets the middle conditional formatting excel based on another cell the conditional formatting in can...

## conditional formatting excel based on another cell