Selecting Cell Content Among Range of Cells Based on Text Format (e.g. Red Text or Yellow Background)

[PKT]

Hi,
I have a range of cells (A2:A5) with text. Of these range of cells, there is always ONE cell that has specific formatting (e.g. RED text, BOLD text, colored BACKGROUND, etc.).The text formatting varies based on the row. All other cells within this range on that specific ROW has black text.
I need to get the content of the cell that has the RED text and store it in cell A1.
Is there a function or formula that can do this in Excel? Thanks.

A1	A2	A3	A4	A5
strawberry	apple	orange	banana	strawberry
football	baseball	football	basketball	soccer

A1	A2	A3	A4	A5
strawberry	apple	orange	banana	strawberry
football	baseball	football	basketball	soccer

A1	A2	A3	A4	A5
strawberry	apple	orange	banana	strawberry
football	baseball	football	basketball	soccer

 

[Micron]

I would say doable with vba. You'd have to clarify whether or not any formatting causes the cell value to be placed in A1 or just certain formatting. So if it's anything but normal it shouldn't be too hard, but if it's based on this, this, this and this but not that, then that is important. Also important is the understanding of the range involved. If it is just A1 to A5 that is not the same as A1:X255 (or the range varies) for example.

 

[PKT]

I would say doable with vba. You'd have to clarify whether or not any formatting causes the cell value to be placed in A1 or just certain formatting. So if it's anything but normal it shouldn't be too hard, but if it's based on this, this, this and this but not that, then that is important. Also important is the understanding of the range involved. If it is just A1 to A5 that is not the same as A1:X255 (or the range varies) for example.O

I would say doable with vba. You'd have to clarify whether or not any formatting causes the cell value to be placed in A1 or just certain formatting. So if it's anything but normal it shouldn't be too hard, but if it's based on this, this, this and this but not that, then that is important. Also important is the understanding of the range involved. If it is just A1 to A5 that is not the same as A1:X255 (or the range varies) for example.

OMG, thanks so much for the quick response!
To answer your question, not any formatting, just one specific type of formatting (e.g. BOLD).
In this case let's say:
- the range of cell contains BOLD text
- the range will vary by rows but at maximum up to 50 columns (A2:A50)
Basically Column A1 should only select cell content from range of cells (A2:A50) that have BOLD text only.

A1	A2	A3	A...	A50
orange, strawberry	apple	orange	banana	strawberry
baseball, football, soccer	baseball	football	basketball	soccer
red, rose pink	blue	red	rose pink	orange
true	true	false
<<formula that extracts bolded text from columns (A2:A50)>>

 

[Micron]

That is slightly more complex because you want to concatenate in column A. However, your A's are column headers? If not then it's not what you show, it's

A1

B1

C1

D1...

AX1

Probably not important. You could try the following code. It looks a bit over-blown to me but it seems to work. Maybe someone else will provide a more condensed version.

Sub findBold()
Dim rng As Range, cel As Range
Dim Lcol As Long, Lrow As Long, i As Integer
Dim str As String

On Error GoTo errHandler
With ActiveSheet
    Lcol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Lrow = Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, _
             SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
End With
Application.EnableEvents = False

i = 2
Set rng = Range(Cells(2, 2), Cells(Lrow, Lcol))
For Each Row In rng.Rows
    For Each cel In Row.Cells
        If Not cel Is Nothing And cel.Font.Bold Then str = str & cel.Value & ", "
    Next cel
    If Not str = "" Then
        str = Trim(str)
        str = Left(str, Len(str) - 1)
        Cells(i, 1) = str
        str = ""
    End If
    i = i + 1
Next Row

exitHere:
Application.EnableEvents = True
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

 

[PKT]

Hi again @Micron, thanks so much for the VBA code!
Yes, the top row is a header row... sorry for the confusion.

On another note, is it possible for this VBA to work with an argument where I can enter various range of cells (e.g. =FindBold(B1:Z1), or =FindBold(B2:Z2), etc...?
So that I can 'drag' the function/formula/VBA down a column? Thanks so much!

A1	B1	C1	D1	Z1
orange, strawberry (e.g. =FindBold(B1:Z1))	apple	orange	banana	strawberry
baseball, football, soccer (e.g. =FindBold(B2:Z2))	baseball	football	basketball	soccer
red, rose pink (e.g. =FindBold(B3:Z3))	blue	red	rose pink	orange
true (e.g. =FindBold(B4:Z4))	true	false
=FindBold(B99:Z99)

 

[Micron]

I can think of 3 ways.
1) A range input box. That pops up, you select a range and click OK on the input and then the code runs.
2) Selection_Change event, but unless you restrict it to a column or row, it would fire every time you select a different cell.
3) userform, but more complicated when the range input should be able to do the same thing

My answers can't be more focused because I don't know the sheet structure or what user actions you might want to use to run the code.