Dim rng as Range
set rng = activecell
Do while isnumeric(rng) and rng.row 1
set rng = rng.offset(-1,0)
Loop
if not isnumeric(rng) then
' rng should point to the cell with "Arrow"
End if

--
Regards,
Tom Ogilvy

"Jimish" wrote:

Now here is the problem
i have diff. heading in column and diff. sub headings in the same
column and all sub headings in diff color. now if i give a range of
data in col. a and sub heading in row 10 then it goes on moving to find
that heading till it reaches the text with diff color.

To make it clear here is the example.

A B C
1 Shirt Qty Jeans Trouser
2 Arrow Levis Arrow
3 32
4 34
5 36
10 Lee Couper

20 Denim J.K

Now in this the main title is Shirt and sub heading is Arrow.and there
are sizes in it now if i select cell b3 as the value(Qty) then it
should automatically type in other sheet(already done that part) the
size and (now the part for which help needed) also give the sub heading
which is in Blue color(Arrow).

Hope the example is clear. So .pls help me in this