Conditional Formatting - SELECTING COLUMN HEADING IF A CELL IS COLORED

[manan]

Hi

I have a excel worksheet which has conditional formatting. If the
condition is true it changes the background color of the cell to pink.I
want a function so that it searchs a row and if any cell in that row is
coloured pink then it should return the value of column heading at the
end of the row.

e.g

a b c d e
1 20 25 30 40 50
2 TOM MOM SOM KOM JOM
3 MOM TOM KOM JOM SOM

Lets assume b3 and d2 are highlighted in pink because of conditional
formatting. I want that in f2 it should return 40 and in f3 it should
return 25

Anybody to help. Its kinda Urgent

 

[Bob Phillips]

Function PinkHeading(rng As Range, ci As Long)
Dim cell As Range
Application.Volatile
For Each cell In rng
If cell.Interior.ColorIndex = ci Then
PinkHeading = Application.Caller.Parent.Cells(1,
cell.Column).Value
Exit For
End If
Next cell
End Function


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[manan]

hi bob
Thanks for ur reply
I am new to excel and not understanding the function.
Though i did understand that the range would be teh selection of row.
What should i selct for "ci". Is it the column heading selection. If it
is the selection of column heading then the function is not returning me
teh values.Instead showing error as VALUE
Also my column headings are not in row 1 they are in row 4.
Thanks

 

[Bob Phillips]

ci should be the colorindex of the colour. For instance, Pink is 7, so for
instance you would use it like

=PinkHeading(4:4,7)

whic h will look for thr first pink cell in row 4, and return the value in
row 1 of that column. If your headings are in row 4, perhaps we should make
that optional, like this

Function PinkHeading(rng As Range, ci As Long, Optional headRow As Long = 1)
Dim cell As Range
Application.Volatile
For Each cell In rng
If cell.Interior.ColorIndex = ci Then
PinkHeading = Application.Caller.Parent _
.Cells(headRow, cell.Column).Value
Exit For
End If
Next cell
End Function

so you can pass the heading row, like

=PinkHeading(A16:M16,7,4)

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[manan]

Hi Bob

Thanks a ton
It worked perfectly. It was really helpful.