Two ways to change background color in Excel based on cell value

In this article, you will find two quick ways to change the background color of cells based on value in Excel 2016, 2013 and 2010. Also, you will learn how to use Excel formulas to change the color of blank cells or cells with formula errors.

Everyone knows that changing the background color of a single cell or a range of data in Excel is easy as clicking the Fill color Fill color icon button . But what if you want to change the background color of all cells with a certain value? Moreover, what if you want the background color to change automatically along with the cell value's changes? Further in this article you will find answers to these questions and learn a couple of useful tips that will help you choose the right method for each particular task.

How to change a cell's color based on value in Excel dynamically

The background color will change dependent on the cell's value.

Task: You have a table or range of data, and you want to change the background color of cells based on cell values. Also, you want the color to change dynamically reflecting the data changes.

Solution: You need to use Excel conditional formatting to highlight the values greater than X, less than Y or between X and Y.

Suppose you have a list of gasoline prices in different states and you want the prices greater than USD 3.7 to be of the color red and equal to or less than USD 3.45 to be of the color green. A table listing gasoline prices in different states

Note: The screenshots for this example were captured in Excel 2010, however the buttons, dialogs and settings are the same or nearly the same in Excel 2016 and Excel 2013.

Okay, here is what you do step-by-step:

  1. Select the table or range where you want to change the background color of cells. In this example, we've selected $B$2:$H$10 (the column names and the first column listing the state names are excluded from the selection).
  2. Navigate to the Home tab, Styles group, and choose Conditional Formatting > New Rule…. On the Home tab, in the Styles group, click Conditional Formatting - New Rule…
  3. In the New Formatting Rule dialog box, select "Format only cells that contain" under "Select a Rule Type" box in the upper part of the dialog box.
  4. In the lower part of the dialog box under "Format Only Cells with section", set the rule conditions. We choose to format only cells with a Cell Value - greater than - 3.7, as you can see in the screenshot below. Select 'Format only cells that contain' and set the rule conditions.

    Then click the Format… button to choose what background color to apply when the above condition is met.

  5. In the Format Cells dialog box, switch to the Fill tab and select the color of your choice, the reddish color in our case, and click OK. In the Format Cells dialog box, on the Fill tab, select the background color of your choice.
  6. Now you are back to the New Formatting Rule window and the preview of your format changes is displayed in the Preview box. If everything is Okay, click the OK button. The preview of format changes is displayed in the Preview box.

    The result of your formatting will look similar to this: The background color of selected cells is changed based on cell values.

    Since we need to apply one more condition, i.e. change the background of cells with values equal to or less than 3.45 to the green color, click the New Rule button again and repeat steps 3 - 6 setting the required condition. Here is the Preview of our second conditional formatting rule: A rule to change the background of cells with values equal to or less than 3.45 to the green color

    When you are done, click the OK button. What you have now is a nicely formatted table that lets you see the highest and lowest gas prices across different states at a glance. Lucky they are in Texas :) The background color is changed based on 2 conditional formatting rules.

    Tip: You can use the same method to change the font color based on the cell's value. To do this, simply switch to the Font tab in the Format Cells dialog box that we discussed in step 5 and choose your preferred font color.

    The font color is changed based on 2 conditional formatting rules.

How to permanently change a cell's color based on its current value

Once set, the background color will not change no matter how the cell's contents might change in the future.

Task: You want to color a cell based on its current value and wish the background color to remain the same even when the cell value's changes.

Solution: Find all cells with a certain value or values using Excel's Find All function or Select Special Cells add-in, and then change the format of found cells using the Format Cells feature.

This is one of those rare tasks that are not covered in Excel help files, forums and blogs and for which there is no straightforward solution. And this is understandable, because this task is not typical. And still, if you need to change the background color of cells statically i.e. once and forever unless you change it manually again, proceed with the following steps.

Find and select all cells that meet a certain condition

There may be several possible scenarios depending on what kind of values you are looking for.

If you need to color cells with a particular value, e.g. 50, 100 or 3.4, go to the Home tab, Editing group, and click Find Select > Find…. Go to the Home tab, Editing group, and click Find Select > Find…

Enter the needed values and click the Find All button. Enter the needed values and click the Find All button.

Tip: Click the Options button in the right-hand part of the Find and Replace dialog to get a number of advanced search options, such as "Match Case" and "Match entire cell content". You can use wildcard characters, such as an asterisk (*) to find any string of characters or a question mark (?) to find any single character.

In our previous example, if we needed to find all gas prices between 3.7 and 3.799, we would specify the following search criteria: Use the wildcard character to find all gas prices between 3.7 and 3.799

Now select any of the found items in the lower part of the Find and Replace dialog window by clicking on it and then press Ctrl + A to select all found entries. After that click the Close button. Select all found items and click the Close button.

This is how you select all cells with a certain value(s) using the Find All function in Excel.

However, what we actually need is to find all gas prices higher than 3.7 and regrettably Excel's Find and Replace dialog does not allow for such things.

Luckily, there is another tool that can handle such complex conditions. The Select Special Cells add-in lets you find all values in a specified range, e.g. between -1 and 45, get the maximum / minimum value in a column, row or range, find cells by font color, fill color and much more. Select Special Cells add-in for Excel

You click the Select by Value button on the ribbon and then specify your search criteria on the add-in's pane, in our example we are looking for values greater than 3.7. Click the Select button and in a second you will have a result like this: Select all values within a particular range using Select Special Cells add-in.

If you are interested to try the Select Special Cells add-in, you can download an evaluation version here.

Change the background color of selected cells using "Format Cells" dialog

Now that all cells with a specified value or values are selected (either by using Excel's Find and Replace or Select Special Cells add-in) what is left for you to do is force the background color of selected cells to change when a value changes.

Open the Format Cells dialog by pressing Ctrl + 1 (you can also right click any of selected cells and choose "Format Cells…" from the pop-up menu, or go to Home tab > Cells group > Format > Format Cells…) and make all format changes you want. We will choose to change the background color in orange this time, just for a change :) Change the background color of selected cells using the Format Cells dialog.

If you want to alter the background color only without any other format changes, then you can simply click the Fill color button and choose the color to your liking. Change the background color of selected cells by clicking the Fill color button.

Here is the result of our format changes in Excel: The backgrounds color of selected cells is changed permanently, regardless of the cell value's changes.

Unlike the previous technique with conditional formatting, the background color set in this way will never change again without your notice, no matter how the values change.

Change background color for special cells (blanks, with formula errors)

Like in the previous example, you can change the background color of special cells in two ways, dynamically and statically.

Use Excel formula to change background color of special cells

A cell's color will change automatically based on the cell's value.

This method provides a solution that you will most likely need in 99% of cases, i.e. the background color of cells will change according to the conditions you set.

We are going to use the gas prices table again as an example, but this time a couple of more states are included and some cells are empty. See how you can detect those blank cells and change their background color.

  1. On the Home tab, in the Styles group, click Conditional Formatting > New Rule… (see step 2 of How to dynamically change a cell color based on value for step-by-step guidance).
  2. In the "New Formatting Rule" dialog, select the option "Use a formula to determine which cells to format". Then enter one of the following formulas in the "Format values where this formula is true" field:
    • =IsBlank()- to change the background color of blank cells.
    • =IsError() - to change the background color of cells with formulas that return errors.

    Since we are interested in changing the color of empty cells, enter the formula =IsBlank(), then place the cursor between parentheses and click the Collapse Dialog button Collapse Dialog icon in the right-hand part of the window to select a range of cells, or you can type the range manually, e.g. =IsBlank(B2:H12). Enter the formula and select a range of cells.

  3. Click the Format… button and choose the needed background color on the Fill tab (for detailed instructions, see step 5 of "How to dynamically change a cell color based on value") and then click OK.

    The preview of your conditional formatting rule will look similar to this: A rule to change the background color of blank cells using a formula

  4. If you are happy with the color, click the OK button and you'll see the changes immediately applied to your table. Click the OK button and you'll see the changes immediately applied to your table.

Change the background color of special cells statically

Once changed, the background color will remain the same, regardless of the cell values' changes.

If you want to change the color of blank cells or cells with formula errors permanently, follow this way.

  1. Select your table or a range and press F5 to open the "Go To" dialog, and then click the "Special…" button. Open the
  2. In the "Go to Special" dialog box, check the Blanks radio button to select all empty cells. Check the Blanks radio button to select all empty cells.

    If you want to highlight cells containing formulas with errors, choose Formulas > Errors. As you can see in the screenshot above, a handful of other options are available to you.

  3. And finally, change the background of selected cells, or make any other format customizations using the "Format Cells" dialog as described in Changing the background of selected cells.

Just remember that formatting changes made in this way will persist even if your blank cells get filled with data or formula errors are corrected. Of course, it's hard to imagine off the top of the head why someone may want to have it this way, may be just for historical purposes :)

How to get most of Excel and make challenging tasks easy

As an active user of Microsoft Excel, you know that it has plenty of features. Some of them we know and love, others are a complete mystery for an average user and various blogs, including this one, are trying to shed at least some light on them. But! There are a few very common tasks that all of us have to perform daily and Excel simply does not provide any features or tools to automate them or make an inch easier.

For example, if you need to check 2 worksheets for duplicates or merge rows from single or different spreadsheets, it would take a bunch of arcane formulas or macros and still there is no guarantee you would get the accurate results.

That was the reason why a team of our best Excel developers designed and created 70+ add-ins that we call the Ultimate Suite for Excel. These smart tools handle the most grueling, painstaking and error-prone tasks in Excel and ensure quickly, neatly and flawless results. Below is a short list of just some of the tasks the add-ins can help you with:

Just try these add-ins and you will see that your Excel productivity will increase up to 50%, at the very least!

That's all for now. In my next article we will continue to explore this topic further and you will see how you can quickly change the background color of a row based on a cell value. Hope to see you on our blog next week!

Table of contents

424 comments

  1. Svetlana
    I can't figure out how to make a cell color change when A1 is 10% greater that A2. I would like A2 to change its color

  2. i want this type of date& day format but i can't create this,please help me:-

    Month---March-15

    Dispatch Date Day
    01 Sunday
    02 Monday
    03 Tuesday
    04 Wednesday
    05 Thursday
    06 Friday
    07 Saturday
    08 Sunday
    09 Monday
    10 Tuesday
    11 Wednesday
    12 Thursday
    13 Friday
    14 Saturday
    15 Sunday

    the formula contain B2(for select the month & year)also contain the formula A5 & B2(i.e contain result 1,2,3,4,5 etc.etc.)
    and day contain formula is on table format(i.e cloum A & B,that's result day sunday,monday etc.)

  3. Hi...Svetlana Cheusheva

    Thanks for all information.
    This is very useful to me.

    Again thanks a lot.

  4. Hi Svetlana,

    I have requirement where in i want to color the cell automatically when i have added some value to other cell. how can i do that?
    like i want to color column X automatically when i will add some value to column Z.

  5. Hi Svetlana,

    I have requirement where in i want to color the cell automatically when i have added some value to other cell. how can i do that?
    like i want to color column X automatically when i will add some value to column Z

  6. Hi Svetlana,
    Thanks for all this info.
    I would like to change the colour of an entire row not just a cell, based on contents of a cell in that row. ie if the specified cell contains a specific word, the entire rows fill can be changed.
    Thanks
    Walter

  7. Can you please attached a filee as reference because i can not follow up with all the comments?

  8. Hi
    I am completely lost when it come to VBA and I need help.
    I have a folder where I save a lot of reports with the same excel format and then I have a master file where I copy and paste all the data from all the folders in. So I have tried a lot of things but cant get the master file to automatically copy the data from all the workbooks.I think where it becomes complex is the workbooks have drop down lists and some color formats in as well in some columns. So what I would like to do is the master file copy all data and past special from the other workbooks and if possible delete them after closing. Please I need help.

  9. Hi Svetlana,
    This is very useful

    I have one question,How to change the entire column color based on one cell value? eg if i have a text sunday in a cell, the entire column should be displayed in different color

    • Hi Hari,

      You can select all the columns you want to highlight and create a rule with the following formula:

      =countif(A:A,"Sunday")>0

      Where A is the left-most column.

  10. Its very use full for me

    thank you

  11. OK Thanks a lot and for your time.
    I did google a lot but did not get any clue. You made it clear :)
    Thanks dear!
    Regards
    AMK

  12. Hi Svetlana,

    Is it possible to change color of cell by using formula alone and not using any conditional formatting.

    Regards
    AMK

    • Hi!

      Nope, formulas can perform various calculations and insert values based on the conditions you specify. To change cells' color you need either conditional formatting or VBA.

  13. Thanks so much...

  14. Hi Great help,

    Just one thing how do I get five category and five colours the conditional formatting is only offering 3 .

  15. Hi Svetlana,

    IS this possible also to put some error message in the cell or in some other cell based upon conditional formatting. let say If cell of column A in a row has value less than 10 then cell of Column B will be highlighted as well as we need to put message that "column A is < 10" either in the same B cell or in different column cell in the same row?

    • Hi Swapnik,

      You can display a message by entering an IF formula in cell B1 and then copying it down to other cells:
      =IF(A1<10, "column A is < 10", "")

  16. thank you

    It's very easy to know

  17. Hi

    I am fully impressed. it's very useful to me thank u very much for simple logic's

  18. Hi Svetlana,

    I have a workbook with multiple sheets of training events. Each sheet of of events makes up a job. All events must be current in order to be qualified for the job. I have conditional formatting set up now to have the training date entered in a cell be green if over 30 days from expiring, yellow if within 30 days and red if past do. I have a master sheet at the beginning of the workbook with a list of all of the employees. I want to list all of our jobs next to their names and have a cell for each job turn red if any of the date values from any of the job sheets turns red indicating something in their jobs qualifications is past do. Is this possible?

  19. Thank you so much..really i got the solution

  20. what is the formula if the condition are
    if the report delivered after 13 of every month than the cell shown red color.can u send me photo like a screen shot

    Thanks

    Best Regards

    Inam ul haq

  21. Typo in the above:

    I need to know "IF" a maximum of 3 has been....

    • Hi Kristen,

      Assuming that the values you want to highlight are in column A and row 2 is your 1st row with data (not including column headers), you can use the following formulas:

      Highlight the 2nd occurrence: =COUNTIF($A$2:$A2,$A2)=2

      Highlight the 3rd and all subsequent occurrences: =COUNTIF($A$2:$A2,$A2)>=3

  22. Hello Svetlana,

    I was wondering if it is possible to get cells in a column to change colour based on whether a word occurs 2 or 3 times in that column. For example, I need to know is a maximum of 3 has been reached (typed into the workbook) so if any word occurs 2 times anywhere in the column I would like it to turn yellow, and if that same word occurs 3 times I would like it to turn red so that I know this item is exhausted. Is this possible?

    Ex. If a given suburb, say "Sydney", has been entered by my sales reps into a column on a shared workbook 3 times I would like to to turn red so that I know it is completed, and if it has been entered 2 times I would like it to turn yellow so that I know it is almost completed.... complicated I know!

  23. Hi Svetlana,

    I noticed that you are a genius in excel and very helpful to others. I was wondering if you could help me out.
    I have a table of numbers and need to fill each cell with a color based on if the previous number increased or decreased. So if 250 (A1) gets changed to 254 (A1), I want to fill in that cell with green because it increased. BUT if that same cell, A1 (254) gets changed to 253, I want the cell to fill in with red because of the decrease.
    I am wondering if this is possible to format for each cell.

    Thank you in advance,
    Tommy

    • Hi Tommy,

      I regret to tell you that it is not possible. Excel conditional formatting formulas can compare a value in a given cell with some other number, or a value in another cell, or a value returned by some other function. But it cannot respond to a value change in the same cell.

      • Ok, thank you for responding so fast! Is there any other way to fill in the cell based on an increase or decrease, that wouldn't have to be applied every day?

  24. Dear Svetlana,

    Congratulate for your blog, I am fully impressed.
    I tried to find an answer and maybe you already had this question - if yes, sorry.

    My question is: I have an excel sheet where costumers populate entries time by time based on general rules, so the entry is fixed. I have a mapping table about 20 countries. I would like to colour only these countries on my main sheet with conditional formatting or a rule-set but without an additional column where I use vlookup. I can set up 20 different rules with conditional formatting, but maybe you have a more professional idea as well.

    Many thanks for your kind help in advance, Kate

    • Hi Kate,

      This task is far from trivial :)

      You can try creating a rule with the following formula:
      =NOT(ISERROR(MATCH(A2,Sheet2!$A$2:$A$20,0)))

      Where A2 is the first cell with data in your main table and A2:A20 is the list of countries in the mapping table.

      • Dear Svetlana,

        You are a genius, it works!!! Thank you very much!

        Kate

  25. What a wonderfully helpful post, and active for so long!

    Now, I have a question. I have a formula to calculate dates out: =WORKDAY(B1+(5-1),1,holidays) So, it will only return weekday results that don't fall on my defined holidays. Is there any way to make it so that when a date is "pushed forward" to a non-holiday weekday, that the cell would change color so you could see at a glance that the date had been pushed forward in that manner?

    Thanks!

  26. Hi Svetlana,

    It is really helpful, Thank you

  27. dear Svetlana,

    i have a question and hopefully you can help me. i have a file in which we have a consequence of numbers in range of 50 (...01-...50; ...51-..100 etc). please find example of how they look below.
    140247951
    140247952
    140247953
    140247954
    140247955
    140247956
    140247957
    140247958
    140247959
    140247960
    140247961
    140247962
    140247963
    140247964
    140247965
    140247966
    140247967
    140247968
    140247969
    140247970

    is it possible to use condition formating to higlight let say 140247970 when i search it through find box (CTRL+F). these are barcodes that we have generated and we want to track them when they have been returned to me.
    hopefully you can help me as i am not very good in these kind of formating.

    thanks in advance :)

  28. thank you its help me....

  29. how do i create a formula where
    if cell B1 = letter z or Z , and no other letter (remove blanks, then change cell color to green

  30. Hi Svetlana,

    I want to know wheather top three selection, or bottom three selections can be displayedin three different colors of our choice

    Thanking you

  31. Hi Svetlana, you have many satisfied customers! I read the couple of hundred posts in your forums and did not quite see the issue I am having. I receive a weekly report and I need to compare 5 columns of data in this week's report vs. the same 5 columns in the prior week's report, and identify if any changes occurred in each row for 900 rows. I use a formula (below) where Column A looks at Column F, Column B looks at Column G, Column C looks at Column H, Column D looks at Column I, and Column E looks at Column J, for any changes (text or number). I put the formula in cell K2 and copy it down to K900. The formula in Column K tells me that some data has changed in the row if there was any change, but I have to visually look at all 10 columns in the row to see what cell(s) data changed. Is there a way to color the cells where the data has changed? Here is the formula I use to identify changes:
    =IF(SUMPRODUCT((A2:A900=F2)*(B2:B900=G2)*(C2:C900=H2)*(D2:D900=I2)*(E2:E900=J2))>0,"","Changed")

    I hope you can help! Thanks

    • Hello George,

      Thanks for the detailed description.
      If you simply need to compare 5 columns in one row, just select A2:E900 or F2:J900 (according to the part of the table you want to color) and create a conditional formatting rule using this formula:
      =A2<>F2

      If the group of 5 cells may be in different rows, then please send your sample workbook to support@ablebits.com. We will try to find the solution.

      • Hi Alexander,
        I actually need to compare 10 columns in one row. I tried the formula you mentioned and variations of it using yellow shading but all it did was shade all the cells yellow. I will send a sample workbook to the email address you provided along with additional clarification of what I need. Thanks.

  32. Thank You so much we solve over problem, before we don't know how to change background color..

    Thank You so Much...

  33. Hi,

    I need help in highlighting multiple cells till a certain value is reached:
    Ex.

    stock demands w01 demands w02 demands w03 demands w04
    3000 500 1000 1200 1000

    I need that Excels highlight first 3 postions because their sum is less than the stock.

    Would someone help me pls?
    Thanks

  34. Hi,

    I am creating a spreadsheet and would like to have three collums with dates that change the color of each row.

    colloum A - turns X color when a date is entered.
    COlloum B - Turns X color when a date is entered.
    Colloum C - Turns X color when a date is entered.

    I would like a check mark to apear in a fourth colloum when the row has three dates entered, and a X when one or two dates are entered.

    Thank you.

    • Hi Aaron,

      You can create a rule for each column in this way: Conditional Formatting > New Rule > Fromat onl cells that contain and choose "No blanks" from the left-most drop down list.

      Then, enter the following formula in the 4th column:
      =IF(AND(A2<>"", B2<>"", C2<>""), 2, IF(AND(A2="", B2="", C2=""), "", 1))

      Where A, B and C are your date columns.

      Finally, create the following icon set rule for the 4th column:
      Icon sets rule

  35. Hi, I have created a worksheet that produces division of an input number by the numbers 1 to 100 in individual cells. I am trying to turn change a cell colour for cells where the product of the division is an integer. I have checked and there does not appear to be a direct way to do this with the standard formulas. Could you help please.

  36. hi,

    i wanted to change the colour of a row based on a specific value of a cell. can i do so?

  37. with an using conditional formula, cell A1 results "passed" with colour letter after putting a date in Cell D1. Please...

  38. Hi there

    I have a set of data in a table and all the numbers are different.

    I'm sending this to someone else and if they wish to manually change the number to something else I want the cell to change colour.

    Is there a way of doing this with conditional formatting?

    Thanks for your help

  39. Svetlana,

    I have a slightly different question, though very related: I would like for cell A1 in Sheet 1 to be red if it is greater than A1 in Sheet 2, and green if it is less than A1 in Sheet 2. I would also like cell B1 in Sheet 1 to change color based on B1 in Sheet 2, and so on.

    I can do this manually, using your instructions for "Format only cells that contain," but I have thousands of cells I would like to change in this way. It may be obvious that I am looking for trend changes, and normally I would use a line chart, but I want to know the exact numbers where the changes take place, and Excel charts don't seem to allow for that kind of detail.

    Thank you for any help you can give.

    Robert

    • Svetlana,

      Nevermind, I figured it out, playing off your suggestion to question #59 (and the basic advice to use the conditional formatting button, of which I had been unaware before I found this post). I used "Use a formula to determine which cells to format," and typed in the formula:

      =A1'Sheet 2'!A1

      For the first formula I selected one color (though it looks like I could have done just about any formatting) and for the second I selected another.

      If you highlight the whole area you need to follow this formatting, and make sure not to use dollar signs on any of the cell coordinates, the formatting should appear across your whole worksheet.

      Thanks for getting me started!

      Robert

      • It looks like I lost a few lines, including the second formula. The first one should have a "greater than" sign after the first "A1," and the second is identical to the first, except it has a "lesser than" sign:

        =A1'Sheet 1'!A1

  40. I have Repeated elements
    5347940
    5347940
    5347945
    5347945
    5348035
    5348035
    5348036
    5348036
    5348053
    in this i want to color alternate numbers

  41. thanks it has been helpfull

  42. Hi Svetlana,

    I am trying to set up a database for training records of employees,

    using your rule above is it possible to set a cell to change green, yellow or red based on the training course expiry length.

    For example Cell E13 is Green, if the date in cell F13 is 60 days away from the course expiry date set in G13 then Cell E13 turns yellow, if it passes the date then it turns red

    Any help is much appreciated

    Thanks

    Craig

    • Hi Craig,

      Try creating a rule for cell E13 with the following formulas:

      Red: =$F13>$G13
      Yellow: =AND($G13-$F13>0, $G13-$F13<=60)
      Green: =$G13-$F13>=60

      If you need to highlight more than 1 cell in column E, select them all before creating a rule and replace "13" in the above formulas with the number of the top row in the selection.

  43. thx for help XD

  44. Thanks a lot
    it was so helpful for me

  45. Can you use this to change a cells color based on a word in the cell instead of a value?

  46. Hi,
    Is there a way to select a random cell in excel and have all the other cell with the same value highlight? So, once I select another cell, the originla highlight is lost and all the cells with values same as the new selected cell gets highlighted.
    I need this for work to make my presentation look spot on and set me apart from the rest. If you could reply back, I would very much appreciate it.
    Thank you!
    Shweta

  47. Svetlana,
    Thank you for your post. It is very useful. You seem to be an expert in excel color and I wonder if I can ask you a question about Excel color. I inherited an excel file with a pale yellow/sand color. When I check the color index, it shows -4142. When I change the color of the cell, it shows the normal color. When I set as no fill, I get this color (-4142). When I print the sheet, the color displays similarly as a no filled cell. I wonder if you know how this -4142 "color" gets set in the first place?
    Thank you in advance.

    • Hello Tina,

      I am hesitant to give any advice without seeing the formatting. If you can send us your workbook at support@ablebits.com, we will hopefully be able to help.

  48. Thank you very much

  49. Hi Svetlana,

    I am very impressed with the creative solutions I have seen on this page.
    I am hoping you can help me with a problem I have been struggling with for months.

    I would like to copy the contents and format of a column of cells and paste them in another column using a formula.
    All columns are formatted as type = General
    Column A is a list of random IP addresses that are color formatted as green or red cells. Just say every other IP address is green and the others red.
    Column C is also a list of IP addresses (a master list) with no color.

    In column B, I want to take all the IP addresses in column A and line them up on the same row as the IP address in column C (and keep the color formatting from column A).
    In column B to get the IP address from column A to line up with the IP addresses in column C, I used the formula:
    =IF(ISNA(MATCH(C1,A:A,0)),"",INDEX(A:A,MATCH(C1,A:A,0)))
    This works but column B does not have the color formatting as the cells in column A
    Is there any way to keep that formatting when using a formula?

    Here is the data (remove column B if you want to check the formual):
    192.168.36.21 192.168.36.21 192.168.36.21
    192.168.32.31 192.168.33.174
    192.168.35.0 192.168.32.31 192.168.32.31
    192.168.37.56 192.168.40.163
    192.168.45.10 192.168.42.82
    192.168.43.221 192.168.35.0 192.168.35.0
    192.168.45.108 192.168.37.56 192.168.37.56
    192.168.46.208 192.168.38.105
    192.168.32.226 192.168.38.214
    192.168.39.208 192.168.43.224
    192.168.32.246 192.168.45.10 192.168.45.10
    192.168.35.109 192.168.43.221 192.168.43.221
    192.168.34.99 192.168.46.215
    192.168.32.55 192.168.45.108 192.168.45.108
    192.168.46.208 192.168.46.208
    192.168.32.226 192.168.32.226
    192.168.43.42
    192.168.39.208 192.168.39.208
    192.168.32.246 192.168.32.246
    192.168.34.23
    192.168.36.68
    192.168.35.109 192.168.35.109
    192.168.34.99 192.168.34.99
    192.168.32.55 192.168.32.55

    Maybe this is not the right approach and using some other way like Offset would be the way to go. Any input is welcome

    Thank You for your valuable time,
    Sven

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