Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
MS Excel
 
Posts: n/a
Default Automatic change color of searched value in a column...

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


  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

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.



MS Excel wrote:

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
  #3   Report Post  
Ragdyer
 
Posts: n/a
Default

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)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"MS Excel" wrote in message
...
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



  #4   Report Post  
MS Excel
 
Posts: n/a
Default

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..


"Ragdyer" wrote in message
...
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)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"MS Excel" wrote in message
...
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






  #5   Report Post  
Bob Phillips
 
Posts: n/a
Default

Use formulae of

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

and

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

--
HTH

Bob Phillips

"MS Excel" wrote in message
...
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..


"Ragdyer" wrote in message
...
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)

--
HTH,

RD


--------------------------------------------------------------------------

-
Please keep all correspondence within the NewsGroup, so all may benefit

!

--------------------------------------------------------------------------

-
"MS Excel" wrote in message
...
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










  #6   Report Post  
MS Excel
 
Posts: n/a
Default

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



"Bob Phillips" wrote in message
...
Use formulae of

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

and

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

--
HTH

Bob Phillips

"MS Excel" wrote in message
...
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..


"Ragdyer" wrote in message
...
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)

--
HTH,

RD


--------------------------------------------------------------------------

-
Please keep all correspondence within the NewsGroup, so all may benefit

!

--------------------------------------------------------------------------

-
"MS Excel" wrote in message
...
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










  #7   Report Post  
olasa
 
Posts: n/a
Default


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

HTH
Ola Sandström


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

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=384271

  #8   Report Post  
olasa
 
Posts: n/a
Default


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...hmentid=&stc=1

HTH
Ola Sandström


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

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=384271

  #9   Report Post  
Bob Phillips
 
Posts: n/a
Default

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


--
HTH

Bob Phillips

"MS Excel" wrote in message
...
Greate!!
But how make it to select only first occurence..



"Bob Phillips" wrote in message
...
Use formulae of

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

and

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

--
HTH

Bob Phillips

"MS Excel" wrote in message
...
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..


"Ragdyer" wrote in message
...
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)

--
HTH,

RD



-------------------------------------------------------------------------

-
-
Please keep all correspondence within the NewsGroup, so all may

benefit
!


-------------------------------------------------------------------------

-
-
"MS Excel" wrote in message
...
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












  #10   Report Post  
MS Excel
 
Posts: n/a
Default

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

Syed

"Bob Phillips" wrote in message
...
That is a conditional function formula, so it will highlight all matching
items. You asked to colour cell, not select.


--
HTH

Bob Phillips

"MS Excel" wrote in message
...
Greate!!
But how make it to select only first occurence..



"Bob Phillips" wrote in message
...
Use formulae of

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

and

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

--
HTH

Bob Phillips

"MS Excel" wrote in message
...
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..


"Ragdyer" wrote in message
...
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)

--
HTH,

RD



-------------------------------------------------------------------------

-
-
Please keep all correspondence within the NewsGroup, so all may

benefit
!


-------------------------------------------------------------------------

-
-
"MS Excel" wrote in message
...
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
















  #11   Report Post  
Bob Phillips
 
Posts: n/a
Default

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

--
HTH

Bob Phillips

"MS Excel" wrote in message
...
Ooopps...I mean to say color rather select..

Syed

"Bob Phillips" wrote in message
...
That is a conditional function formula, so it will highlight all

matching
items. You asked to colour cell, not select.


--
HTH

Bob Phillips

"MS Excel" wrote in message
...
Greate!!
But how make it to select only first occurence..



"Bob Phillips" wrote in message
...
Use formulae of

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

and

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

--
HTH

Bob Phillips

"MS Excel" wrote in message
...
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..


"Ragdyer" wrote in message
...
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)

--
HTH,

RD




------------------------------------------------------------------------

-
-
-
Please keep all correspondence within the NewsGroup, so all may

benefit
!



------------------------------------------------------------------------

-
-
-
"MS Excel" wrote in message
...
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
















  #12   Report Post  
olasa
 
Posts: n/a
Default


I saw that my previous link was not working.

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

Ola


--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=384271

  #13   Report Post  
MS Excel
 
Posts: n/a
Default

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


"olasa" wrote in
message ...

I saw that my previous link was not working.

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

Ola


--
olasa
------------------------------------------------------------------------
olasa's Profile:
http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=384271



  #14   Report Post  
olasa
 
Posts: n/a
Default


Thanks for the feedback. Happy it helped.
Ola


--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=384271

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
HOW DO I CHANGE THE AUTOMATIC BORDER COLOR IN EXCEL? mhbento Excel Worksheet Functions 1 March 16th 05 05:55 PM
When I change the style in column (a) a date, it also changes colu Wayne Excel Discussion (Misc queries) 4 January 31st 05 04:05 PM
automatic color change in cells using a drop down list kennethwt Excel Worksheet Functions 1 January 21st 05 07:37 PM
Automatic Change of X-axis Jan Charts and Charting in Excel 3 January 16th 05 08:01 PM
Is there an automatic way to change numbers such as 12312001 t curtev Excel Discussion (Misc queries) 1 December 29th 04 11:32 PM


All times are GMT +1. The time now is 07:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"