Automatic change color of searched value in a column...

[MS Excel]

Per below list, i want to change colour of a cell whose value matches with
CELL A1 contents. For example:

A1 = 239.05
Column B
1) 235.66
2) 154.60
3) 239.05
4) 522.05

As I enter 239.05 in cell A1 it automatically change colour of CELL B3
(that's equal to 239.05). Any possibility?
Be noted that it should seach for the first occurence only.. also what
amendment can enable the proposed solution to search for all first & next
occurence ?


Regards,
Syed

 

[Dave Peterson]

You can use Format|Conditional formatting.

Select column B and with B1 active:
format|Conditional formatting
Cell value is:
(type) =$a$1

Format the way you want.

 

[Ragdyer]

To highlight *only* the first occurrence of the cell that matches the data
in A1,
Select Column B, from B1 to whatever, with the focus of the selection in B1
(colored white), then:
<Format> <Conditional Format>,
Change "Cell Value Is" to "Formula Is",
And enter this in the next box:

=AND(COUNTIF($B$1:B1,$A$1)=1,$A$1=B1)

Click on "Format" and choose whatever format you wish,
Then <OK> <OK>

To highlight only the first *2 matches*, enter this formula:

=AND(OR(COUNTIF($B$1:B1,$A$1)=1,COUNTIF($B$1:B1,$A$1)=2),$A$1=B1)

 

[MS Excel]

THANKS FOR YOUR HELP..

One more thing, how to automatically change color of item which have same
value in different column... like in below data data in B1 is equal to C3
and B4 is equal to C1.
Simply the proposed solution should match a column with another column and
change color of matched cells.

Column COLUMN
B C
1) 235.66 552.05
2) 154.60 623.00
3) 239.05 235.66
4) 522.05 154.60

Hope I am clear..

 

[Bob Phillips]

Use formulae of

=ISNUMBER(MATCH(B1,C:C,0))

and

=ISNUMBER(MATCH(C1,B:B,0))

 

[MS Excel]

Greate!!
But how make it to select only first occurence..

 

[olasa]

Here is one solution.
See encl. zip-file
http://www.excelforum.com/attachment.php?attachmentid=3566&stc=1

HTH
Ola Sandströ

+-------------------------------------------------------------------
|Filename: Book5.zip
|Download: http://www.excelforum.com/attachment.php?postid=3566
+-------------------------------------------------------------------

 

[olasa]

Here is one solution:
=AND(COUNTIF($C$1:$C$7,B1)<>0,COUNTIF($B$1:B1,B1)<=1)

See encl. zip-file
http://www.excelforum.com/attachment.php?attachmentid=&stc=1

HTH
Ola Sandströ

+-------------------------------------------------------------------
|Filename: Book5.zip
|Download: http://www.excelforum.com/attachment.php?postid=3568
+-------------------------------------------------------------------

 

[Bob Phillips]

That is a conditional function formula, so it will highlight all matching
items. You asked to colour cell, not select.

 

[MS Excel]

Ooopps...I mean to say color rather select..

Syed

 

[Bob Phillips]

As I said, this formula will colour all such occurrences.

 

[olasa]

I saw that my previous link was not working.

Is this what you want?
http://www.excelforum.com/attachment.php?attachmentid=3568&stc=1

Ola

 

[MS Excel]

Yes its working... its really very helpfull.. thanks to both of you for
helping..

 

[olasa]

Thanks for the feedback. Happy it helped.
Ola