Highlight entire row

[Guest]

Hi,

I have a series of data alphanumeric in various columns from A2 to I125
(aaprox.). The same has many rows.
I want that in cell B1 when I enter certain data (either numeric or
alphanumeric or just words) the same should search in the data below (from A2
to I125) and highlight the rows (or the rows gets filled with certain
colours) which has that data entered in B1.

Cheers,
Mandeep

 

[Max]

One CF play ..

Select rows 2 to 125 (with A2 active)
and use the cond format formula:
=ISNUMBER(MATCH($B$1,2:2,0))
Format pattern to taste, OK out

 

[Guest]

Thanks Max for the reply.
But sorry to say the result is not as per expected.
With the formula given by you, all the rows gets coloured and when I enter
data in B1 all the rows get back to normal i.e., without any colour.
What I wanted is that the formula should work as Find option, where in the
entire database should be searched and where ever the data is matched with
that entered in B1 the entire row should get highlighted.
If the formula does not work then can the MACRO be written for the same. If
yes, could you help with the same.

Cheers,
Mandeep

 

[RagDyer]

You're doing something wrong because Max's formula works *exactly* as you
requested.

Where exactly are you entering the formula?

 

[Guest]

Hi Rag,

I have tried again and not getting the result as desired.
I am entering the formula in Conditional Formating --> Formula Is and then
selecting the colour from Format option.
As instructed I have selected all the rows that have data from row no. 2. In
selecting all the rows from row no. 2, cell A2 is the one selected.
Just to add further there are certain cells empty in the database that I
have. The empty cells could be in any columns. There is no set pattern.

Hope I am able to convey what I have done.

Cheers,
Mandeep

 

[Max]

RD - Thanks for the step-in !

Mandeep, here's a small sample file
with the implemented construct for rows 2 - 10:
http://cjoint.com/?ljiMn2Vazx
HighlightEntireRow_Mandeep_gen.xls

Test it out by inputting: test123 (that's the value in B1)
in any cell within rows 2 - 10, and the green CF fill will be triggered for
that entire row. If you delete the test input, the fill will disappear.

Select rows 2 to 125 (with A2 active)

You should select the row *headers*, starting from row header 2 down to
header 125 (this makes A2 the active cell), then just click Format > Cond
Formatting, and enter the suggested formula, etc.

It should work ok, try it again ..

--

 

[Guest]

Thanks a lot Max for the effort.
It works exactly as I wanted.
Now there is another issue bit related to the use of this formula.
I have a data in .txt format, have created a macro to import the same data
in excel file. When I am using the above formula the same is not working.
This is the reason why I was not able to understand the formula in your
first reply.
Is there some thing to do with the import of data from .txt format to excel
format that this formula is not working.

Cheers,
Mandeep

 

[Max]

There's probably some whitespaces somewhere within the imported data

Try instead, as the cond format formula:
=SUMPRODUCT(--(TRIM(2:2)=$B$1))>0

 

[Max]

Try instead, as the cond format formula:

=SUMPRODUCT(--(TRIM(2:2)=$B$1))>0

And .. just in case there might even be whitespaces inadvertently introduced
in the entry made in the reference cell B1, perhaps we better wrap TRIM()
around it as well <g>, so try the slightly longer:
=SUMPRODUCT(--(TRIM(2:2)=TRIM($B$1)))>0