Set Range only where cells have bold font

[Eserino]

Hello, can anyone help me (if its even possible) to set range for other excel integrated functions, like MAX, LARGE,... in one row, but only where the values are bold? Is it even possible?

I have a table where are 20+ columns and some of the values in a single row are bold. (the count of bold values/cells changes every day)
I need to apply i.e. function MAX on each row, but only for bolded values.
It would be helpfull, if I could get the cells with bold value as range reference.

Example: =MAX(onlyBoldCells)

Thanks anyone who can help me.

 

[Comfy]

I think you'll need to use some VBA

Public Function BMAX(rng As Range)
Dim mRng As Range
Dim c As Range
For Each c In rng
    If c.Font.Bold Then
        If mRng Is Nothing Then
            Set mRng = c
        Else
            Set mRng = Union(mRng, c)
        End If
    End If
Next c


If mRng Is Nothing Then
    BMAX = 0
Else
    BMAX = Application.WorksheetFunction.Max(mRng)
End If


End Function

WARNING!!
The formulas will not update if the cell formatting is changed. You will need to recalculate.

Excel 2010
	A	B	C	D	E	F	G
1	1	2	3	4	5	5	1
2	1	2	3	4	5	5	4
3	1	2	3	4	5	5	2
4	1	2	3	4	5	5	5
Sheet2

Cell Formulas
Range	Formula
F1	=MAX(A1:E1)
G1	=BMAX(A1:E1)

 

[gsbelbin]

You can do this on the sheet using a helper column and the GET.CELL function but it doesn't update if you change the bold settings of the cells, you need to recalculate. So rather than go through the fine details of how to do this can I ask a question first? You must have some logic to decide which cells are bold so can you not use this to decide which cells to include in the range?

 

[Eserino]

I think you'll need to use some VBA

Public Function BMAX(rng As Range)
Dim mRng As Range
Dim c As Range
For Each c In rng
    If c.Font.Bold Then
        If mRng Is Nothing Then
            Set mRng = c
        Else
            Set mRng = Union(mRng, c)
        End If
    End If
Next c


If mRng Is Nothing Then
    BMAX = 0
Else
    BMAX = Application.WorksheetFunction.Max(mRng)
End If


End Function

WARNING!!
The formulas will not update if the cell formatting is changed. You will need to recalculate.

Excel 2010

	A	B	C	D	E	F	G

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F1[/TH]
[TD="align: left"]=MAX(A1:E1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G1[/TH]
[TD="align: left"]=BMAX(A1:E1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks Comfy,

I am glad, I had the same approach, I got the same code (I modified some code I googled) except aplying
"BMAX = Application.WorksheetFunction.Max(mRng)"

This will work, but is it possible to get the value of the variable "mRng" to be used as range for other formulas?
I.E. =LARGE(mRng.value;2)/MAX(mRng.value) without to modify VB code for every change/excelFunction?

Or with other words, how can be the function BMAX modified, so that the output will every time be a range?

Usage like this:

=LARGE(BMAX(A1:E1),2)/MAX(BMAX(A1:E1))

Is it possible to get the result of BMAX like $B$2,$B$3,$B$4 or $B$2:$B$4 (coresponding to your image/table)
Or I.E. $R$1:$R$5,$R$7,$R$11

Does it have to be an array or what?
Thanks for help.

 

[Eserino]

Hi,
really it deosn't have any logic. I get every day some tables where other people "bold" some values (each time different)
I already read the topic of ussage of GET.CELL, but it is, as you mentioned, some conditional logic behind.

 

[Comfy]

Something like this:

Excel 2010
	E	F	G	H	I	J	K
12	1	2	3	4	5	6	4
13	1	2	3	4	5	9	4
14	1	2	3	4	5	#VALUE!	#VALUE!
15	1	2	3	4	5	6	3
16	1	2	3	4	5	14	5
Sheet2

Cell Formulas
Range	Formula
J12	=SUM(BRNG(E12:I12))
K12	=MAX(BRNG(E12:I12))

Public Function BRNG(rng As Range) As Range
Dim mRng As Range
Dim c As Range
For Each c In rng
    If c.Font.Bold Then
        If mRng Is Nothing Then
            Set mRng = c
        Else
            Set mRng = Union(mRng, c)
        End If
    End If
Next c


    Set BRNG = mRng
End Function

 

[Comfy]

#VALUE will be returned because mRng Is Nothing.

How do you want to handle ranges where non of the cells are Bold?

I'm not sure is returning zero is the answer, so left it as an error for now.

 

[Eserino]

Thank you very much.
Now I am ashamed
Grrrr, got this 2 hours ago, but I forgot to change the variable to range.
Sorry guys for consuming your time. Not that I am writing VB code every day.

 

[Eserino]

#VALUE will be returned because mRng Is Nothing.

How do you want to handle ranges where non of the cells are Bold?

I'm not sure is returning zero is the answer, so left it as an error for now.

Thats the smallest problem.
There must be at least one value bold. I know it is not comfort from excel point of view, but for now it is OK.