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.



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

[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)

--
HTH,

RD

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

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


"Ragdyer" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>

[Bob Phillips]

Use formulae of

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

and

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

--
HTH

Bob Phillips

"MS Excel" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >
>
>
>

[MS Excel]

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



"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Use formulae of
>
> =ISNUMBER(MATCH(B1,C:C,0))
>
> and
>
> =ISNUMBER(MATCH(C1,B:B,0))
>
> --
> HTH
>
> Bob Phillips
>
> "MS Excel" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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
>> >>
>> >>
>> >
>>
>>
>>
>
>

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

HTH
Ola Sandström

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> Greate!!
> But how make it to select only first occurence..
>
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Use formulae of
> >
> > =ISNUMBER(MATCH(B1,C:C,0))
> >
> > and
> >
> > =ISNUMBER(MATCH(C1,B:B,0))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "MS Excel" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> 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
> >> >>
> >> >>
> >> >
> >>
> >>
> >>
> >
> >
>
>

[MS Excel]

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

Syed

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> Greate!!
>> But how make it to select only first occurence..
>>
>>
>>
>> "Bob Phillips" <[email protected]> wrote in message
>> news:[email protected]...
>> > Use formulae of
>> >
>> > =ISNUMBER(MATCH(B1,C:C,0))
>> >
>> > and
>> >
>> > =ISNUMBER(MATCH(C1,B:B,0))
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > "MS Excel" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> 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
>> >> >>
>> >> >>
>> >> >
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Bob Phillips]

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

--
HTH

Bob Phillips

"MS Excel" <[email protected]> wrote in message
news:[email protected]...
> Ooopps...I mean to say color rather select..
>
> Syed
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> Greate!!
> >> But how make it to select only first occurence..
> >>
> >>
> >>
> >> "Bob Phillips" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Use formulae of
> >> >
> >> > =ISNUMBER(MATCH(B1,C:C,0))
> >> >
> >> > and
> >> >
> >> > =ISNUMBER(MATCH(C1,B:B,0))
> >> >
> >> > --
> >> > HTH
> >> >
> >> > Bob Phillips
> >> >
> >> > "MS Excel" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> 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" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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" <[email protected]> wrote in message
> >> >> > news:[email protected]...
> >> >> >> 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
> >> >> >>
> >> >> >>
> >> >> >
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

[olasa]

I saw that my previous link was not working.

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

Ola

[MS Excel]

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


"olasa" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[olasa]

Thanks for the feedback. Happy it helped.
Ola