| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
populate cell on worksheet 2 if text is red on worksheet 1
I have the 2007 calendar (jan - dec) on one worksheet (master worksheet). The
text is colored depending on the team it applies to. Copied worksheet 1 to become Worksheet 2, 3 and 4 (one for each team). Here is the ideal solution: Worksheet 2 (team 1) If cells C5:H110 on Worksheet 1 contain text that is RED, populate C5:H110 on Worksheet 2 with only RED text. Worksheet 3 (team 2) If cells C5:H110 on Worksheet 1 contain text that is GREEN, populate C5:H110 on Worksheet 3 with only GREEN text. Worksheet 4 (team 3) If cells C5:H110 on Worksheet 1 contain text that is BLUE, populate C5:H110 on Worksheet 4 with only RED text. -- Thank you, scrowley(AT)littleonline.com |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
populate cell on worksheet 2 if text is red on worksheet 1
S,
The general strategy would be to use a macro - you will need to figure out the correct colorindices to use. (Hint: use the macro recorder while applying the font colors...) Sub CalColors() Dim myCell As Range Worksheets("Sheet1").Range("C5:H110").ClearContent s Worksheets("Sheet2").Range("C5:H110").ClearContent s Worksheets("Sheet3").Range("C5:H110").ClearContent s Worksheets("Sheet4").Range("C5:H110").ClearContent s For Each myCell In Worksheets("Master").Range("C5:H110") If myCell.Font.ColorIndex = 3 Then Worksheets("Sheet1").Range(myCell.Address).Value = myCell.Value End If If myCell.Font.ColorIndex = 6 Then Worksheets("Sheet2").Range(myCell.Address).Value = myCell.Value End If If myCell.Font.ColorIndex = 8 Then Worksheets("Sheet3").Range(myCell.Address).Value = myCell.Value End If If myCell.Font.ColorIndex = 10 Then Worksheets("Sheet4").Range(myCell.Address).Value = myCell.Value End If Next myCell End Sub -- HTH, Bernie MS Excel MVP "SCrowley" wrote in message ... I have the 2007 calendar (jan - dec) on one worksheet (master worksheet). The text is colored depending on the team it applies to. Copied worksheet 1 to become Worksheet 2, 3 and 4 (one for each team). Here is the ideal solution: Worksheet 2 (team 1) If cells C5:H110 on Worksheet 1 contain text that is RED, populate C5:H110 on Worksheet 2 with only RED text. Worksheet 3 (team 2) If cells C5:H110 on Worksheet 1 contain text that is GREEN, populate C5:H110 on Worksheet 3 with only GREEN text. Worksheet 4 (team 3) If cells C5:H110 on Worksheet 1 contain text that is BLUE, populate C5:H110 on Worksheet 4 with only RED text. -- Thank you, scrowley(AT)littleonline.com |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
populate cell on worksheet 2 if text is red on worksheet 1
Bernie - You saved my life!!!
This works perfectly. I commented out the ClearContents lines due to they are already populated and I don't want to lose the data. Here is how my final code looks: Sub CalColors() Dim myCell As Range 'Worksheets("2007 Calendar").Range("B5:H110").ClearContents 'Worksheets("Retail").Range("B5:H110").ClearConten ts 'Worksheets("Workplace").Range("B5:H110").ClearCon tents 'Worksheets("Community").Range("B5:H110").ClearCon tents For Each myCell In Worksheets("2007 Calendar").Range("B5:H110") 'If myCell.Font.ColorIndex = 3 Then 'Worksheets("2007 Calendar").Range(myCell.Address).Value = myCell.Value 'End If If myCell.Font.ColorIndex = 3 Then Worksheets("Retail").Range(myCell.Address).Value = myCell.Value End If If myCell.Font.ColorIndex = 5 Then Worksheets("Workplace").Range(myCell.Address).Valu e = myCell.Value End If If myCell.Font.ColorIndex = 10 Then Worksheets("Community").Range(myCell.Address).Valu e = myCell.Value End If Next myCell End Sub -- Thank you, scrowley(AT)littleonline.com "Bernie Deitrick" wrote: S, The general strategy would be to use a macro - you will need to figure out the correct colorindices to use. (Hint: use the macro recorder while applying the font colors...) Sub CalColors() Dim myCell As Range Worksheets("Sheet1").Range("C5:H110").ClearContent s Worksheets("Sheet2").Range("C5:H110").ClearContent s Worksheets("Sheet3").Range("C5:H110").ClearContent s Worksheets("Sheet4").Range("C5:H110").ClearContent s For Each myCell In Worksheets("Master").Range("C5:H110") If myCell.Font.ColorIndex = 3 Then Worksheets("Sheet1").Range(myCell.Address).Value = myCell.Value End If If myCell.Font.ColorIndex = 6 Then Worksheets("Sheet2").Range(myCell.Address).Value = myCell.Value End If If myCell.Font.ColorIndex = 8 Then Worksheets("Sheet3").Range(myCell.Address).Value = myCell.Value End If If myCell.Font.ColorIndex = 10 Then Worksheets("Sheet4").Range(myCell.Address).Value = myCell.Value End If Next myCell End Sub -- HTH, Bernie MS Excel MVP "SCrowley" wrote in message ... I have the 2007 calendar (jan - dec) on one worksheet (master worksheet). The text is colored depending on the team it applies to. Copied worksheet 1 to become Worksheet 2, 3 and 4 (one for each team). Here is the ideal solution: Worksheet 2 (team 1) If cells C5:H110 on Worksheet 1 contain text that is RED, populate C5:H110 on Worksheet 2 with only RED text. Worksheet 3 (team 2) If cells C5:H110 on Worksheet 1 contain text that is GREEN, populate C5:H110 on Worksheet 3 with only GREEN text. Worksheet 4 (team 3) If cells C5:H110 on Worksheet 1 contain text that is BLUE, populate C5:H110 on Worksheet 4 with only RED text. -- Thank you, scrowley(AT)littleonline.com |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
populate cell on worksheet 2 if text is red on worksheet 1
Bernie,
You saved my life! This works beautifully. I made a couple of modifications to fit my needs: Sub CalColors() Dim myCell As Range ' Worksheets("2007 Calendar").Range("B5:H110").ClearContents ' Worksheets("Retail").Range("B5:H110").ClearContent s ' Worksheets("Workplace").Range("B5:H110").ClearCont ents ' Worksheets("Community").Range("B5:H110").ClearCont ents For Each myCell In Worksheets("2007 Calendar").Range("B5:H110") If myCell.Font.ColorIndex = 3 Then Worksheets("Retail").Range(myCell.Address).Value = myCell.Value End If If myCell.Font.ColorIndex = 5 Then Worksheets("Workplace").Range(myCell.Address).Valu e = myCell.Value End If If myCell.Font.ColorIndex = 10 Then Worksheets("Community").Range(myCell.Address).Valu e = myCell.Value End If Next myCell End Sub -- Thank you, scrowley(AT)littleonline.com "Bernie Deitrick" wrote: S, The general strategy would be to use a macro - you will need to figure out the correct colorindices to use. (Hint: use the macro recorder while applying the font colors...) Sub CalColors() Dim myCell As Range Worksheets("Sheet1").Range("C5:H110").ClearContent s Worksheets("Sheet2").Range("C5:H110").ClearContent s Worksheets("Sheet3").Range("C5:H110").ClearContent s Worksheets("Sheet4").Range("C5:H110").ClearContent s For Each myCell In Worksheets("Master").Range("C5:H110") If myCell.Font.ColorIndex = 3 Then Worksheets("Sheet1").Range(myCell.Address).Value = myCell.Value End If If myCell.Font.ColorIndex = 6 Then Worksheets("Sheet2").Range(myCell.Address).Value = myCell.Value End If If myCell.Font.ColorIndex = 8 Then Worksheets("Sheet3").Range(myCell.Address).Value = myCell.Value End If If myCell.Font.ColorIndex = 10 Then Worksheets("Sheet4").Range(myCell.Address).Value = myCell.Value End If Next myCell End Sub -- HTH, Bernie MS Excel MVP "SCrowley" wrote in message ... I have the 2007 calendar (jan - dec) on one worksheet (master worksheet). The text is colored depending on the team it applies to. Copied worksheet 1 to become Worksheet 2, 3 and 4 (one for each team). Here is the ideal solution: Worksheet 2 (team 1) If cells C5:H110 on Worksheet 1 contain text that is RED, populate C5:H110 on Worksheet 2 with only RED text. Worksheet 3 (team 2) If cells C5:H110 on Worksheet 1 contain text that is GREEN, populate C5:H110 on Worksheet 3 with only GREEN text. Worksheet 4 (team 3) If cells C5:H110 on Worksheet 1 contain text that is BLUE, populate C5:H110 on Worksheet 4 with only RED text. -- Thank you, scrowley(AT)littleonline.com |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
populate cell on worksheet 2 if text is red on worksheet 1
Bernie,
I passed this on to the end user. Now they want to separate if there are multiple colors in one cell. Can this same concept apply? If so, how would it look for multiples? -- Thank you, scrowley(AT)littleonline.com "Bernie Deitrick" wrote: S, The general strategy would be to use a macro - you will need to figure out the correct colorindices to use. (Hint: use the macro recorder while applying the font colors...) Sub CalColors() Dim myCell As Range Worksheets("Sheet1").Range("C5:H110").ClearContent s Worksheets("Sheet2").Range("C5:H110").ClearContent s Worksheets("Sheet3").Range("C5:H110").ClearContent s Worksheets("Sheet4").Range("C5:H110").ClearContent s For Each myCell In Worksheets("Master").Range("C5:H110") If myCell.Font.ColorIndex = 3 Then Worksheets("Sheet1").Range(myCell.Address).Value = myCell.Value End If If myCell.Font.ColorIndex = 6 Then Worksheets("Sheet2").Range(myCell.Address).Value = myCell.Value End If If myCell.Font.ColorIndex = 8 Then Worksheets("Sheet3").Range(myCell.Address).Value = myCell.Value End If If myCell.Font.ColorIndex = 10 Then Worksheets("Sheet4").Range(myCell.Address).Value = myCell.Value End If Next myCell End Sub -- HTH, Bernie MS Excel MVP "SCrowley" wrote in message ... I have the 2007 calendar (jan - dec) on one worksheet (master worksheet). The text is colored depending on the team it applies to. Copied worksheet 1 to become Worksheet 2, 3 and 4 (one for each team). Here is the ideal solution: Worksheet 2 (team 1) If cells C5:H110 on Worksheet 1 contain text that is RED, populate C5:H110 on Worksheet 2 with only RED text. Worksheet 3 (team 2) If cells C5:H110 on Worksheet 1 contain text that is GREEN, populate C5:H110 on Worksheet 3 with only GREEN text. Worksheet 4 (team 3) If cells C5:H110 on Worksheet 1 contain text that is BLUE, populate C5:H110 on Worksheet 4 with only RED text. -- Thank you, scrowley(AT)littleonline.com |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
populate cell on worksheet 2 if text is red on worksheet 1
S,
Try this, which will look at font colors on a letter by letter basis. Note that if the string is 100 characters long, all in color 3, then this will copy the information over 100 times. We could add controls to only do the copying once if this is really slow..... Also, I wasn't sure what you meant by 'separate' - do you only want the words that are the specific color?? Sub CalColors2() Dim myCell As Range Dim i As Integer For Each myCell In Worksheets("2007 Calendar").Range("B5:H110") For i = 1 To Len(myCell.Value) If myCell.Characters(Start:=i, Length:=1).Font.ColorIndex = 3 Then Worksheets("Retail").Range(myCell.Address).Value = myCell.Value End If If myCell.Characters(Start:=i, Length:=1).Font.ColorIndex = 5 Then Worksheets("Workplace").Range(myCell.Address).Valu e = myCell.Value End If If myCell.Characters(Start:=i, Length:=1).Font.ColorIndex = 10 Then Worksheets("Community").Range(myCell.Address).Valu e = myCell.Value End If Next i Next myCell End Sub HTH, Bernie MS Excel MVP "SCrowley" wrote in message ... Bernie, I passed this on to the end user. Now they want to separate if there are multiple colors in one cell. Can this same concept apply? If so, how would it look for multiples? -- Thank you, scrowley(AT)littleonline.com "Bernie Deitrick" wrote: S, The general strategy would be to use a macro - you will need to figure out the correct colorindices to use. (Hint: use the macro recorder while applying the font colors...) Sub CalColors() Dim myCell As Range Worksheets("Sheet1").Range("C5:H110").ClearContent s Worksheets("Sheet2").Range("C5:H110").ClearContent s Worksheets("Sheet3").Range("C5:H110").ClearContent s Worksheets("Sheet4").Range("C5:H110").ClearContent s For Each myCell In Worksheets("Master").Range("C5:H110") If myCell.Font.ColorIndex = 3 Then Worksheets("Sheet1").Range(myCell.Address).Value = myCell.Value End If If myCell.Font.ColorIndex = 6 Then Worksheets("Sheet2").Range(myCell.Address).Value = myCell.Value End If If myCell.Font.ColorIndex = 8 Then Worksheets("Sheet3").Range(myCell.Address).Value = myCell.Value End If If myCell.Font.ColorIndex = 10 Then Worksheets("Sheet4").Range(myCell.Address).Value = myCell.Value End If Next myCell End Sub -- HTH, Bernie MS Excel MVP "SCrowley" wrote in message ... I have the 2007 calendar (jan - dec) on one worksheet (master worksheet). The text is colored depending on the team it applies to. Copied worksheet 1 to become Worksheet 2, 3 and 4 (one for each team). Here is the ideal solution: Worksheet 2 (team 1) If cells C5:H110 on Worksheet 1 contain text that is RED, populate C5:H110 on Worksheet 2 with only RED text. Worksheet 3 (team 2) If cells C5:H110 on Worksheet 1 contain text that is GREEN, populate C5:H110 on Worksheet 3 with only GREEN text. Worksheet 4 (team 3) If cells C5:H110 on Worksheet 1 contain text that is BLUE, populate C5:H110 on Worksheet 4 with only RED text. -- Thank you, scrowley(AT)littleonline.com |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
populate cell on worksheet 2 if text is red on worksheet 1
Thank you, Bernie.
I'll try this solution. To answer your question: Cell B5 has 4 lines: Ln1 = Washington Post (in RED, hard return to next line); Ln2 = Chicago Tribune (in GREEN, hard return to next line); Ln3 = Charlotte Observer (in BLUE, hard return to next line); Ln4 = Atlanta Constitution (in BLACK) Each color represents a specific team. The Master Sheet houses all the teams data and the team sheets represent only the data that corrolates with their specified colored font. Does this help? -- Thank you, scrowley(AT)littleonline.com "Bernie Deitrick" wrote: S, Try this, which will look at font colors on a letter by letter basis. Note that if the string is 100 characters long, all in color 3, then this will copy the information over 100 times. We could add controls to only do the copying once if this is really slow..... Also, I wasn't sure what you meant by 'separate' - do you only want the words that are the specific color?? Sub CalColors2() Dim myCell As Range Dim i As Integer For Each myCell In Worksheets("2007 Calendar").Range("B5:H110") For i = 1 To Len(myCell.Value) If myCell.Characters(Start:=i, Length:=1).Font.ColorIndex = 3 Then Worksheets("Retail").Range(myCell.Address).Value = myCell.Value End If If myCell.Characters(Start:=i, Length:=1).Font.ColorIndex = 5 Then Worksheets("Workplace").Range(myCell.Address).Valu e = myCell.Value End If If myCell.Characters(Start:=i, Length:=1).Font.ColorIndex = 10 Then Worksheets("Community").Range(myCell.Address).Valu e = myCell.Value End If Next i Next myCell End Sub HTH, Bernie MS Excel MVP "SCrowley" wrote in message ... Bernie, I passed this on to the end user. Now they want to separate if there are multiple colors in one cell. Can this same concept apply? If so, how would it look for multiples? -- Thank you, scrowley(AT)littleonline.com "Bernie Deitrick" wrote: S, The general strategy would be to use a macro - you will need to figure out the correct colorindices to use. (Hint: use the macro recorder while applying the font colors...) Sub CalColors() Dim myCell As Range Worksheets("Sheet1").Range("C5:H110").ClearContent s Worksheets("Sheet2").Range("C5:H110").ClearContent s Worksheets("Sheet3").Range("C5:H110").ClearContent s Worksheets("Sheet4").Range("C5:H110").ClearContent s For Each myCell In Worksheets("Master").Range("C5:H110") If myCell.Font.ColorIndex = 3 Then Worksheets("Sheet1").Range(myCell.Address).Value = myCell.Value End If If myCell.Font.ColorIndex = 6 Then Worksheets("Sheet2").Range(myCell.Address).Value = myCell.Value End If If myCell.Font.ColorIndex = 8 Then Worksheets("Sheet3").Range(myCell.Address).Value = myCell.Value End If If myCell.Font.ColorIndex = 10 Then Worksheets("Sheet4").Range(myCell.Address).Value = myCell.Value End If Next myCell End Sub -- HTH, Bernie MS Excel MVP "SCrowley" wrote in message ... I have the 2007 calendar (jan - dec) on one worksheet (master worksheet). The text is colored depending on the team it applies to. Copied worksheet 1 to become Worksheet 2, 3 and 4 (one for each team). Here is the ideal solution: Worksheet 2 (team 1) If cells C5:H110 on Worksheet 1 contain text that is RED, populate C5:H110 on Worksheet 2 with only RED text. Worksheet 3 (team 2) If cells C5:H110 on Worksheet 1 contain text that is GREEN, populate C5:H110 on Worksheet 3 with only GREEN text. Worksheet 4 (team 3) If cells C5:H110 on Worksheet 1 contain text that is BLUE, populate C5:H110 on Worksheet 4 with only RED text. -- Thank you, scrowley(AT)littleonline.com |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
populate cell on worksheet 2 if text is red on worksheet 1
S,
No, it doesn't answer my question. What data should be copied to the other sheets from cell B5? Option 1: All four lines to all four sheets. Option 2: Line one to the red sheet, line 2 to the green sheet, etc. Will all text be colored, or just some words? and what to do with the default text? HTH, Bernie MS Excel MVP "SCrowley" wrote in message ... Thank you, Bernie. I'll try this solution. To answer your question: Cell B5 has 4 lines: Ln1 = Washington Post (in RED, hard return to next line); Ln2 = Chicago Tribune (in GREEN, hard return to next line); Ln3 = Charlotte Observer (in BLUE, hard return to next line); Ln4 = Atlanta Constitution (in BLACK) Each color represents a specific team. The Master Sheet houses all the teams data and the team sheets represent only the data that corrolates with their specified colored font. Does this help? -- Thank you, scrowley(AT)littleonline.com "Bernie Deitrick" wrote: S, Try this, which will look at font colors on a letter by letter basis. Note that if the string is 100 characters long, all in color 3, then this will copy the information over 100 times. We could add controls to only do the copying once if this is really slow..... Also, I wasn't sure what you meant by 'separate' - do you only want the words that are the specific color?? Sub CalColors2() Dim myCell As Range Dim i As Integer For Each myCell In Worksheets("2007 Calendar").Range("B5:H110") For i = 1 To Len(myCell.Value) If myCell.Characters(Start:=i, Length:=1).Font.ColorIndex = 3 Then Worksheets("Retail").Range(myCell.Address).Value = myCell.Value End If If myCell.Characters(Start:=i, Length:=1).Font.ColorIndex = 5 Then Worksheets("Workplace").Range(myCell.Address).Valu e = myCell.Value End If If myCell.Characters(Start:=i, Length:=1).Font.ColorIndex = 10 Then Worksheets("Community").Range(myCell.Address).Valu e = myCell.Value End If Next i Next myCell End Sub HTH, Bernie MS Excel MVP "SCrowley" wrote in message ... Bernie, I passed this on to the end user. Now they want to separate if there are multiple colors in one cell. Can this same concept apply? If so, how would it look for multiples? -- Thank you, scrowley(AT)littleonline.com "Bernie Deitrick" wrote: S, The general strategy would be to use a macro - you will need to figure out the correct colorindices to use. (Hint: use the macro recorder while applying the font colors...) Sub CalColors() Dim myCell As Range Worksheets("Sheet1").Range("C5:H110").ClearContent s Worksheets("Sheet2").Range("C5:H110").ClearContent s Worksheets("Sheet3").Range("C5:H110").ClearContent s Worksheets("Sheet4").Range("C5:H110").ClearContent s For Each myCell In Worksheets("Master").Range("C5:H110") If myCell.Font.ColorIndex = 3 Then Worksheets("Sheet1").Range(myCell.Address).Value = myCell.Value End If If myCell.Font.ColorIndex = 6 Then Worksheets("Sheet2").Range(myCell.Address).Value = myCell.Value End If If myCell.Font.ColorIndex = 8 Then Worksheets("Sheet3").Range(myCell.Address).Value = myCell.Value End If If myCell.Font.ColorIndex = 10 Then Worksheets("Sheet4").Range(myCell.Address).Value = myCell.Value End If Next myCell End Sub -- HTH, Bernie MS Excel MVP "SCrowley" wrote in message ... I have the 2007 calendar (jan - dec) on one worksheet (master worksheet). The text is colored depending on the team it applies to. Copied worksheet 1 to become Worksheet 2, 3 and 4 (one for each team). Here is the ideal solution: Worksheet 2 (team 1) If cells C5:H110 on Worksheet 1 contain text that is RED, populate C5:H110 on Worksheet 2 with only RED text. Worksheet 3 (team 2) If cells C5:H110 on Worksheet 1 contain text that is GREEN, populate C5:H110 on Worksheet 3 with only GREEN text. Worksheet 4 (team 3) If cells C5:H110 on Worksheet 1 contain text that is BLUE, populate C5:H110 on Worksheet 4 with only RED text. -- Thank you, scrowley(AT)littleonline.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I populate a worksheet name in a formula using a cell ref. | Excel Discussion (Misc queries) | |||
| Populate cell with Worksheet name | Excel Discussion (Misc queries) | |||
| how do I populate a cell with data from another worksheet, | Excel Discussion (Misc queries) | |||
| how do I populate a cell with data from another worksheet, | Excel Discussion (Misc queries) | |||
| How to populate a cell with the name of the worksheet it is in | Excel Discussion (Misc queries) | |||
Hybrid Mode
