Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
highlighting changes by color in an Excel column
Hi Everyone,
I have a task that I have been doing by hand that is tedious and inaccurate, and I am hoping a clever reader will be able to help me. I have several columns of data in an Excel table that I would like to quickly analyze and determine were the differences are. For example, I might have 20 rows, and in column 1 I might have 19 'A' and 1 'V'; in the next column I might have 6 'D', 8 'E', 5 'N', and 1 'Q', etc. What I do by hand is highlight each change with a different color so that I can track how and where they change. Is there a macro that could do this? It is very tedious and time consuming (not to mention in accurate) for me to do this by hand, particularly when dealing with several hunderd columns and a 100 or so rows. Thank you in advance. PS I have never written a macro, I just know that they are amazing! Warm regards, Eric |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
highlighting changes by color in an Excel column
This might help: to find differences in two or more columns, hold down
the ctrl button and select entire columns in turn then press: Ctrl+\ (Edit Goto Special Column Differences). The cells that are selected are those that differ from the comparison column i.e. the one that contains the active cell. To remove column headings from the selection press Ctrl+Shift+\ (Edit Goto Special Row Differences) once or as many times as is required. You can then format all the selected cells in a different colour. Eric wrote: Hi Everyone, I have a task that I have been doing by hand that is tedious and inaccurate, and I am hoping a clever reader will be able to help me. I have several columns of data in an Excel table that I would like to quickly analyze and determine were the differences are. For example, I might have 20 rows, and in column 1 I might have 19 'A' and 1 'V'; in the next column I might have 6 'D', 8 'E', 5 'N', and 1 'Q', etc. What I do by hand is highlight each change with a different color so that I can track how and where they change. Is there a macro that could do this? It is very tedious and time consuming (not to mention in accurate) for me to do this by hand, particularly when dealing with several hunderd columns and a 100 or so rows. Thank you in advance. PS I have never written a macro, I just know that they are amazing! Warm regards, Eric |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
highlighting changes by color in an Excel column
If you have 3 or fewer conditions you want to apply you can use conditional
formatting, via Formatting--Conditional Formatting. More than three--then you have to write a macro... Dave -- Brevity is the soul of wit. "Eric" wrote: Hi Everyone, I have a task that I have been doing by hand that is tedious and inaccurate, and I am hoping a clever reader will be able to help me. I have several columns of data in an Excel table that I would like to quickly analyze and determine were the differences are. For example, I might have 20 rows, and in column 1 I might have 19 'A' and 1 'V'; in the next column I might have 6 'D', 8 'E', 5 'N', and 1 'Q', etc. What I do by hand is highlight each change with a different color so that I can track how and where they change. Is there a macro that could do this? It is very tedious and time consuming (not to mention in accurate) for me to do this by hand, particularly when dealing with several hunderd columns and a 100 or so rows. Thank you in advance. PS I have never written a macro, I just know that they are amazing! Warm regards, Eric |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
highlighting changes by color in an Excel column
Hi Lori,
Thank you, that method helps. Eric Lori wrote: This might help: to find differences in two or more columns, hold down the ctrl button and select entire columns in turn then press: Ctrl+\ (Edit Goto Special Column Differences). The cells that are selected are those that differ from the comparison column i.e. the one that contains the active cell. To remove column headings from the selection press Ctrl+Shift+\ (Edit Goto Special Row Differences) once or as many times as is required. You can then format all the selected cells in a different colour. Eric wrote: Hi Everyone, I have a task that I have been doing by hand that is tedious and inaccurate, and I am hoping a clever reader will be able to help me. I have several columns of data in an Excel table that I would like to quickly analyze and determine were the differences are. For example, I might have 20 rows, and in column 1 I might have 19 'A' and 1 'V'; in the next column I might have 6 'D', 8 'E', 5 'N', and 1 'Q', etc. What I do by hand is highlight each change with a different color so that I can track how and where they change. Is there a macro that could do this? It is very tedious and time consuming (not to mention in accurate) for me to do this by hand, particularly when dealing with several hunderd columns and a 100 or so rows. Thank you in advance. PS I have never written a macro, I just know that they are amazing! Warm regards, Eric |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
highlighting changes by color in an Excel column
Hi Dave,
Yes, i have tried the conditioonal formatting with mixed results. It does save some time, but is still tedious over several hundered columns. How would I write a macro to do it? Can you suggest a tutorial? Thank you, Eric Dave F wrote: If you have 3 or fewer conditions you want to apply you can use conditional formatting, via Formatting--Conditional Formatting. More than three--then you have to write a macro... Dave -- Brevity is the soul of wit. "Eric" wrote: Hi Everyone, I have a task that I have been doing by hand that is tedious and inaccurate, and I am hoping a clever reader will be able to help me. I have several columns of data in an Excel table that I would like to quickly analyze and determine were the differences are. For example, I might have 20 rows, and in column 1 I might have 19 'A' and 1 'V'; in the next column I might have 6 'D', 8 'E', 5 'N', and 1 'Q', etc. What I do by hand is highlight each change with a different color so that I can track how and where they change. Is there a macro that could do this? It is very tedious and time consuming (not to mention in accurate) for me to do this by hand, particularly when dealing with several hunderd columns and a 100 or so rows. Thank you in advance. PS I have never written a macro, I just know that they are amazing! Warm regards, Eric |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
highlighting changes by color in an Excel column
This assumes each column is sorted - which your description seems to imply.
Select the second row of data through the last row of data Make a note of the active cell - say it's A3 Use FormatConditional Formatting Select "cell value is" "not equal to" and enter =A2 (or what ever cell is immediately above your active cell) Click on the format button and go to the pattern tab. Select an appropriate color and OK your way back to the spreadsheet. You should see highlights in each column where the value changes "Eric" wrote: Hi Everyone, I have a task that I have been doing by hand that is tedious and inaccurate, and I am hoping a clever reader will be able to help me. I have several columns of data in an Excel table that I would like to quickly analyze and determine were the differences are. For example, I might have 20 rows, and in column 1 I might have 19 'A' and 1 'V'; in the next column I might have 6 'D', 8 'E', 5 'N', and 1 'Q', etc. What I do by hand is highlight each change with a different color so that I can track how and where they change. Is there a macro that could do this? It is very tedious and time consuming (not to mention in accurate) for me to do this by hand, particularly when dealing with several hunderd columns and a 100 or so rows. Thank you in advance. PS I have never written a macro, I just know that they are amazing! Warm regards, Eric |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
highlighting changes by color in an Excel column
Eric
You have twice mentioned hundreds of columns. Versions of Excel prior to 2007 have only a maximum of 256 columns. Are you using 2007 version? Just curious.................maybe you have rows and columns mixed up? Gord Dibben MS Excel MVP On 6 Nov 2006 13:51:50 -0800, "Eric" wrote: Hi Dave, Yes, i have tried the conditioonal formatting with mixed results. It does save some time, but is still tedious over several hundered columns. How would I write a macro to do it? Can you suggest a tutorial? Thank you, Eric Dave F wrote: If you have 3 or fewer conditions you want to apply you can use conditional formatting, via Formatting--Conditional Formatting. More than three--then you have to write a macro... Dave -- Brevity is the soul of wit. "Eric" wrote: Hi Everyone, I have a task that I have been doing by hand that is tedious and inaccurate, and I am hoping a clever reader will be able to help me. I have several columns of data in an Excel table that I would like to quickly analyze and determine were the differences are. For example, I might have 20 rows, and in column 1 I might have 19 'A' and 1 'V'; in the next column I might have 6 'D', 8 'E', 5 'N', and 1 'Q', etc. What I do by hand is highlight each change with a different color so that I can track how and where they change. Is there a macro that could do this? It is very tedious and time consuming (not to mention in accurate) for me to do this by hand, particularly when dealing with several hunderd columns and a 100 or so rows. Thank you in advance. PS I have never written a macro, I just know that they are amazing! Warm regards, Eric |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
highlighting changes by color in an Excel column
Hi Gord,
No, I am using a version prior to 2007, and I divide my columns into different worksheets to get around the 256 limitation. What I am looking at are columns of amino acids within a multiple alignment, so I am really interested in how these change. Thus, columns that are completely identical are discarded and only those with variation are highlighted. The problem is that some of the columns have five or more differences, and I cannot find an easy way to highlight all of the changes as different colors. Do you have any ideas? Thanks, Eric Gord Dibben wrote: Eric You have twice mentioned hundreds of columns. Versions of Excel prior to 2007 have only a maximum of 256 columns. Are you using 2007 version? Just curious.................maybe you have rows and columns mixed up? Gord Dibben MS Excel MVP On 6 Nov 2006 13:51:50 -0800, "Eric" wrote: Hi Dave, Yes, i have tried the conditioonal formatting with mixed results. It does save some time, but is still tedious over several hundered columns. How would I write a macro to do it? Can you suggest a tutorial? Thank you, Eric Dave F wrote: If you have 3 or fewer conditions you want to apply you can use conditional formatting, via Formatting--Conditional Formatting. More than three--then you have to write a macro... Dave -- Brevity is the soul of wit. "Eric" wrote: Hi Everyone, I have a task that I have been doing by hand that is tedious and inaccurate, and I am hoping a clever reader will be able to help me. I have several columns of data in an Excel table that I would like to quickly analyze and determine were the differences are. For example, I might have 20 rows, and in column 1 I might have 19 'A' and 1 'V'; in the next column I might have 6 'D', 8 'E', 5 'N', and 1 'Q', etc. What I do by hand is highlight each change with a different color so that I can track how and where they change. Is there a macro that could do this? It is very tedious and time consuming (not to mention in accurate) for me to do this by hand, particularly when dealing with several hunderd columns and a 100 or so rows. Thank you in advance. PS I have never written a macro, I just know that they are amazing! Warm regards, Eric |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
highlighting changes by color in an Excel column
Eric
Maybe similar to this. Add Cases and colors to suit. Option Compare Text Sub colorit() Dim cel As Range ActiveSheet.Range("A1:IV100").Select For Each cel In Selection Select Case cel.Value Case Is = "19 'A'": Num = 10 'green Case Is = "2 'V'": Num = 6 'yellow Case Is = "6 'D'": Num = 5 'blue Case Is = "8 'E'": Num = 7 'magenta Case Is = "1 'Q'": Num = 46 'orange Case Is = "5 'N'": Num = 3 'red Case Else: Num = -4142 'no color End Select 'Apply the color cel.Interior.ColorIndex = Num Next cel End Sub Gord On 7 Nov 2006 12:04:48 -0800, "Eric" wrote: Hi Gord, No, I am using a version prior to 2007, and I divide my columns into different worksheets to get around the 256 limitation. What I am looking at are columns of amino acids within a multiple alignment, so I am really interested in how these change. Thus, columns that are completely identical are discarded and only those with variation are highlighted. The problem is that some of the columns have five or more differences, and I cannot find an easy way to highlight all of the changes as different colors. Do you have any ideas? Thanks, Eric Gord Dibben wrote: Eric You have twice mentioned hundreds of columns. Versions of Excel prior to 2007 have only a maximum of 256 columns. Are you using 2007 version? Just curious.................maybe you have rows and columns mixed up? Gord Dibben MS Excel MVP On 6 Nov 2006 13:51:50 -0800, "Eric" wrote: Hi Dave, Yes, i have tried the conditioonal formatting with mixed results. It does save some time, but is still tedious over several hundered columns. How would I write a macro to do it? Can you suggest a tutorial? Thank you, Eric Dave F wrote: If you have 3 or fewer conditions you want to apply you can use conditional formatting, via Formatting--Conditional Formatting. More than three--then you have to write a macro... Dave -- Brevity is the soul of wit. "Eric" wrote: Hi Everyone, I have a task that I have been doing by hand that is tedious and inaccurate, and I am hoping a clever reader will be able to help me. I have several columns of data in an Excel table that I would like to quickly analyze and determine were the differences are. For example, I might have 20 rows, and in column 1 I might have 19 'A' and 1 'V'; in the next column I might have 6 'D', 8 'E', 5 'N', and 1 'Q', etc. What I do by hand is highlight each change with a different color so that I can track how and where they change. Is there a macro that could do this? It is very tedious and time consuming (not to mention in accurate) for me to do this by hand, particularly when dealing with several hunderd columns and a 100 or so rows. Thank you in advance. PS I have never written a macro, I just know that they are amazing! Warm regards, Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro | Excel Discussion (Misc queries) | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
Excel should provide an easy way to switch a column into a row, n. | Excel Discussion (Misc queries) |