How to display value in a range with a specific format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to get the value within a range of the item that satisfies a formatting condition

In this example below, I want cell A6 to return the value of the cell within range A1..A4 that is highlighted in yellow

A1 Dog - highlighted in yello
A2 Cat - no highligh
A3 Mouse - no highligh
A4 Wildebeest - no highligh
A5 ---------------
A6 Do

To keep things simple, assume that there is always only one item within a range that is highlighted

I tried manipulating some code for summing cells that are colored green, for example, and that worked - but I couldn't translate the code into doing what I want it to

Thanks a lot
R Astrid
 
Hi

Have a look at Chip's site:

http://www.cpearson.com/excel/colors.htm

Andy

R Astrid said:
I need to get the value within a range of the item that satisfies a formatting condition.

In this example below, I want cell A6 to return the value of the cell
Click to expand...
within range A1..A4 that is highlighted in yellow:
A1 Dog - highlighted in yellow
A2 Cat - no highlight
A3 Mouse - no highlight
A4 Wildebeest - no highlight
A5 ----------------
A6 Dog

To keep things simple, assume that there is always only one item within a range that is highlighted.

I tried manipulating some code for summing cells that are colored green,
Click to expand...
for example, and that worked - but I couldn't translate the code into doing
what I want it to.
 
one way:

Public Function ReturnColor(ByRef rRange As Range, _
optional byVal nColorIndex As Long = 6) As Variant
Dim rCell As Range
Application.Volatile
For Each rCell In rRange
If rCell.Interior.ColorIndex = nColorIndex Then
ReturnColor = rCell.Value
Exit For
End If
Next rCell
If ReturnColor = Empty Then ReturnColor = CVErr(xlErrNA)
End Function

Note that this will not update automatically when you change cell colors
- it will update on the first calculation. The function will by default
use the (default) yellow color, but you can specify nColorIndex from 1
to 56.


Call as

=ReturnColor(A1:A4, 6)


or

=ReturnColor(A1:A4, 3)

to return a cell highlighted in Red
 
Back
Top