| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to concatenate based on font color
I have imported a csv file that splits the names into 3 different colomns. I
am trying to merge the names back together but only when it meets certain criteria. Cannot figure out how to make the following formula work only on cells that have a font ColorIndex of 3. Have searched but can't find quite the right answer. Thanks in advance for your help. Sub ConcatColor() lastrow = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lastrow '<<assumes header row Range("P" & i) = Cells(i, 7) & " " & Cells(i, 8) & " " & Cells(i, 9) Next End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trying to concatenate based on font color
Need value.
Range("P" & i) = Cells(i, 7).Value & " " & Cells(i, 8).Value & " " & Cells(i, 9).Value "jeremiah" wrote: I have imported a csv file that splits the names into 3 different colomns. I am trying to merge the names back together but only when it meets certain criteria. Cannot figure out how to make the following formula work only on cells that have a font ColorIndex of 3. Have searched but can't find quite the right answer. Thanks in advance for your help. Sub ConcatColor() lastrow = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lastrow '<<assumes header row Range("P" & i) = Cells(i, 7) & " " & Cells(i, 8) & " " & Cells(i, 9) Next End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trying to concatenate based on font color
Hi,
I'm not sure which cells you want to check the colorindex of but this may get you going in the right direction For i = 2 To lastrow '<<assumes header row If Range("P" & i).Interior.ColorIndex = 3 Then Range("P" & i) = Cells(i, 7) & " " & Cells(i, 8) & " " & Cells(i, 9) Next Mike "jeremiah" wrote: I have imported a csv file that splits the names into 3 different colomns. I am trying to merge the names back together but only when it meets certain criteria. Cannot figure out how to make the following formula work only on cells that have a font ColorIndex of 3. Have searched but can't find quite the right answer. Thanks in advance for your help. Sub ConcatColor() lastrow = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lastrow '<<assumes header row Range("P" & i) = Cells(i, 7) & " " & Cells(i, 8) & " " & Cells(i, 9) Next End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trying to concatenate based on font color
Thanks so much, I knew it was a very simple fix. I did have a compile error
but inserted an End If and works perfectly. "Mike H" wrote: Hi, I'm not sure which cells you want to check the colorindex of but this may get you going in the right direction For i = 2 To lastrow '<<assumes header row If Range("P" & i).Interior.ColorIndex = 3 Then Range("P" & i) = Cells(i, 7) & " " & Cells(i, 8) & " " & Cells(i, 9) Next Mike "jeremiah" wrote: I have imported a csv file that splits the names into 3 different colomns. I am trying to merge the names back together but only when it meets certain criteria. Cannot figure out how to make the following formula work only on cells that have a font ColorIndex of 3. Have searched but can't find quite the right answer. Thanks in advance for your help. Sub ConcatColor() lastrow = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lastrow '<<assumes header row Range("P" & i) = Cells(i, 7) & " " & Cells(i, 8) & " " & Cells(i, 9) Next End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trying to concatenate based on font color
Hi,
Thanks for the feedback. I never tested it so missed the missing End If. Mike "jeremiah" wrote: Thanks so much, I knew it was a very simple fix. I did have a compile error but inserted an End If and works perfectly. "Mike H" wrote: Hi, I'm not sure which cells you want to check the colorindex of but this may get you going in the right direction For i = 2 To lastrow '<<assumes header row If Range("P" & i).Interior.ColorIndex = 3 Then Range("P" & i) = Cells(i, 7) & " " & Cells(i, 8) & " " & Cells(i, 9) Next Mike "jeremiah" wrote: I have imported a csv file that splits the names into 3 different colomns. I am trying to merge the names back together but only when it meets certain criteria. Cannot figure out how to make the following formula work only on cells that have a font ColorIndex of 3. Have searched but can't find quite the right answer. Thanks in advance for your help. Sub ConcatColor() lastrow = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lastrow '<<assumes header row Range("P" & i) = Cells(i, 7) & " " & Cells(i, 8) & " " & Cells(i, 9) Next End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Changing Font color based on font type or size | Excel Discussion (Misc queries) | |||
| change font color based on a value | Excel Worksheet Functions | |||
| How do I concatenate text to keep the text's original color/font/s | Excel Discussion (Misc queries) | |||
| Sum based on font color | Excel Programming | |||
| Portion of concatenate formula to have different color and font fo | Excel Worksheet Functions | |||
Linear Mode
