Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change cell background color based on content that results from li
This could be very simple, but I will lay the groundwork first. I have a
schedule spreadsheet that I import data to from a web based program. There are existing filters to remove all formatting of the data and remove that which we do not use. What remains is a non formatted sheet that other workbooks link to so we can produce daily sheets. Now the data that is linked on the other sheets may be for example the number 150. It appears throughout the sheet and I would like to color any cell that contains the number 150. The problem is I can't search for 150 because it really doesn't exist. All that exists is the linked cell refernece to where this page gets the data from. The action I want to learn to produce is that, based on the resulting data, color the cell(s) the chosen color. Something to the effect of if any cell in workbook1 contains number 150, resulting from a link to a cell in workbooka1, to be colored blue. This makes every occurance of 150 in a cell change the cell background blue. I would like to have this formulated for about 25 conditions. I am willing to do all the work, all the training, all the coding, whatever I need to so that I can make this work as we use this weekly and have to manually alter background colors. I don't want to buy something to do it since once I establish this formatting code it will not change very often if at all. Thank you in advance for any guidance and help you might provide! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change cell background color based on content that results from li
For that many conditions, you'll want to use a macro most likely. Since this
will be imported data, I'll assume you'll run this macro after the import. First, here's the macro you can use: '================ Sub FormatColors() For Each c In ActiveSheet.UsedRange xCheck = c.Value With c.Interior Select Case xCheck 'Each case represents the value to look for 'and the ColorIndex corresponds to the color 'you want the background to be Case 100 ..ColorIndex = 6 Case 150 ..ColorIndex = 9 'Repeat as necessary.... Case Else 'If not a previous condition, no fill ..ColorIndex = 0 End Select End With Next End Sub '================ 'To create a key showing which number corresponds to which color 'you can run this quick macro, which uses row number to represent color 'Run this macro on a blank sheet Sub CreateKey() For i = 1 to 56 Cells(i,1).Interior.ColorIndex = i Next End Sub -- Best Regards, Luke M "joemc911" wrote in message ... This could be very simple, but I will lay the groundwork first. I have a schedule spreadsheet that I import data to from a web based program. There are existing filters to remove all formatting of the data and remove that which we do not use. What remains is a non formatted sheet that other workbooks link to so we can produce daily sheets. Now the data that is linked on the other sheets may be for example the number 150. It appears throughout the sheet and I would like to color any cell that contains the number 150. The problem is I can't search for 150 because it really doesn't exist. All that exists is the linked cell refernece to where this page gets the data from. The action I want to learn to produce is that, based on the resulting data, color the cell(s) the chosen color. Something to the effect of if any cell in workbook1 contains number 150, resulting from a link to a cell in workbooka1, to be colored blue. This makes every occurance of 150 in a cell change the cell background blue. I would like to have this formulated for about 25 conditions. I am willing to do all the work, all the training, all the coding, whatever I need to so that I can make this work as we use this weekly and have to manually alter background colors. I don't want to buy something to do it since once I establish this formatting code it will not change very often if at all. Thank you in advance for any guidance and help you might provide! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change cell background color based on content that results fro
Luke,
Would you be so kind to send me your E-Mail address to: micky-a <at tapuz.co.il Thanks, Micky "Luke M" wrote: For that many conditions, you'll want to use a macro most likely. Since this will be imported data, I'll assume you'll run this macro after the import. First, here's the macro you can use: '================ Sub FormatColors() For Each c In ActiveSheet.UsedRange xCheck = c.Value With c.Interior Select Case xCheck 'Each case represents the value to look for 'and the ColorIndex corresponds to the color 'you want the background to be Case 100 ..ColorIndex = 6 Case 150 ..ColorIndex = 9 'Repeat as necessary.... Case Else 'If not a previous condition, no fill ..ColorIndex = 0 End Select End With Next End Sub '================ 'To create a key showing which number corresponds to which color 'you can run this quick macro, which uses row number to represent color 'Run this macro on a blank sheet Sub CreateKey() For i = 1 to 56 Cells(i,1).Interior.ColorIndex = i Next End Sub -- Best Regards, Luke M "joemc911" wrote in message ... This could be very simple, but I will lay the groundwork first. I have a schedule spreadsheet that I import data to from a web based program. There are existing filters to remove all formatting of the data and remove that which we do not use. What remains is a non formatted sheet that other workbooks link to so we can produce daily sheets. Now the data that is linked on the other sheets may be for example the number 150. It appears throughout the sheet and I would like to color any cell that contains the number 150. The problem is I can't search for 150 because it really doesn't exist. All that exists is the linked cell refernece to where this page gets the data from. The action I want to learn to produce is that, based on the resulting data, color the cell(s) the chosen color. Something to the effect of if any cell in workbook1 contains number 150, resulting from a link to a cell in workbooka1, to be colored blue. This makes every occurance of 150 in a cell change the cell background blue. I would like to have this formulated for about 25 conditions. I am willing to do all the work, all the training, all the coding, whatever I need to so that I can make this work as we use this weekly and have to manually alter background colors. I don't want to buy something to do it since once I establish this formatting code it will not change very often if at all. Thank you in advance for any guidance and help you might provide! . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change cell background color based on content that results fro
Thank you so much for the answer Luke! It appears right in line with what I
thought. May I make one more request, though. While I understand what you are saying to do I don't know exactly how to do it. I don't know how to create and run the macro nor how to use the macro to give me a color key page. I am imagining I have to paste the code you provided into VB somehow. I just don't have any real training or idea how to do this. I could use pointers on the color key page as well, but am guessing this might just be transparent once I learn the other part. In any case, thanks again for such quick help! I'm lloking forward to being able to put it in to practice! Joe M "Luke M" wrote: For that many conditions, you'll want to use a macro most likely. Since this will be imported data, I'll assume you'll run this macro after the import. First, here's the macro you can use: '================ Sub FormatColors() For Each c In ActiveSheet.UsedRange xCheck = c.Value With c.Interior Select Case xCheck 'Each case represents the value to look for 'and the ColorIndex corresponds to the color 'you want the background to be Case 100 ..ColorIndex = 6 Case 150 ..ColorIndex = 9 'Repeat as necessary.... Case Else 'If not a previous condition, no fill ..ColorIndex = 0 End Select End With Next End Sub '================ 'To create a key showing which number corresponds to which color 'you can run this quick macro, which uses row number to represent color 'Run this macro on a blank sheet Sub CreateKey() For i = 1 to 56 Cells(i,1).Interior.ColorIndex = i Next End Sub -- Best Regards, Luke M "joemc911" wrote in message ... This could be very simple, but I will lay the groundwork first. I have a schedule spreadsheet that I import data to from a web based program. There are existing filters to remove all formatting of the data and remove that which we do not use. What remains is a non formatted sheet that other workbooks link to so we can produce daily sheets. Now the data that is linked on the other sheets may be for example the number 150. It appears throughout the sheet and I would like to color any cell that contains the number 150. The problem is I can't search for 150 because it really doesn't exist. All that exists is the linked cell refernece to where this page gets the data from. The action I want to learn to produce is that, based on the resulting data, color the cell(s) the chosen color. Something to the effect of if any cell in workbook1 contains number 150, resulting from a link to a cell in workbooka1, to be colored blue. This makes every occurance of 150 in a cell change the cell background blue. I would like to have this formulated for about 25 conditions. I am willing to do all the work, all the training, all the coding, whatever I need to so that I can make this work as we use this weekly and have to manually alter background colors. I don't want to buy something to do it since once I establish this formatting code it will not change very often if at all. Thank you in advance for any guidance and help you might provide! . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change cell background color based on content that results fro
Hi Joemc911
The link below is a sample worksheet with Luke M's macro. Slightly modified so you can play with it. http://cjoint.com/?dyplLsnupa Let us know if that is really what you want. HTH John P.S. I may not be able to help you with macros, I'm just starting to learn, but someone else will, if your questions are clear with examples. "joemc911" wrote in message ... Thank you so much for the answer Luke! It appears right in line with what I thought. May I make one more request, though. While I understand what you are saying to do I don't know exactly how to do it. I don't know how to create and run the macro nor how to use the macro to give me a color key page. I am imagining I have to paste the code you provided into VB somehow. I just don't have any real training or idea how to do this. I could use pointers on the color key page as well, but am guessing this might just be transparent once I learn the other part. In any case, thanks again for such quick help! I'm lloking forward to being able to put it in to practice! Joe M "Luke M" wrote: For that many conditions, you'll want to use a macro most likely. Since this will be imported data, I'll assume you'll run this macro after the import. First, here's the macro you can use: '================ Sub FormatColors() For Each c In ActiveSheet.UsedRange xCheck = c.Value With c.Interior Select Case xCheck 'Each case represents the value to look for 'and the ColorIndex corresponds to the color 'you want the background to be Case 100 ..ColorIndex = 6 Case 150 ..ColorIndex = 9 'Repeat as necessary.... Case Else 'If not a previous condition, no fill ..ColorIndex = 0 End Select End With Next End Sub '================ 'To create a key showing which number corresponds to which color 'you can run this quick macro, which uses row number to represent color 'Run this macro on a blank sheet Sub CreateKey() For i = 1 to 56 Cells(i,1).Interior.ColorIndex = i Next End Sub -- Best Regards, Luke M "joemc911" wrote in message ... This could be very simple, but I will lay the groundwork first. I have a schedule spreadsheet that I import data to from a web based program. There are existing filters to remove all formatting of the data and remove that which we do not use. What remains is a non formatted sheet that other workbooks link to so we can produce daily sheets. Now the data that is linked on the other sheets may be for example the number 150. It appears throughout the sheet and I would like to color any cell that contains the number 150. The problem is I can't search for 150 because it really doesn't exist. All that exists is the linked cell refernece to where this page gets the data from. The action I want to learn to produce is that, based on the resulting data, color the cell(s) the chosen color. Something to the effect of if any cell in workbook1 contains number 150, resulting from a link to a cell in workbooka1, to be colored blue. This makes every occurance of 150 in a cell change the cell background blue. I would like to have this formulated for about 25 conditions. I am willing to do all the work, all the training, all the coding, whatever I need to so that I can make this work as we use this weekly and have to manually alter background colors. I don't want to buy something to do it since once I establish this formatting code it will not change very often if at all. Thank you in advance for any guidance and help you might provide! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change background color of cell based on vlookup in cell | Excel Discussion (Misc queries) | |||
change background color based on range value | New Users to Excel | |||
change the color of cell background based on a result | Excel Worksheet Functions | |||
Change background color based on value in column A | Excel Discussion (Misc queries) | |||
how to change a cell color based on its content using macro? | New Users to Excel |