Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW DO I CHANGE THE AUTOMATIC BORDER COLOR IN EXCEL? | Excel Worksheet Functions | |||
When I change the style in column (a) a date, it also changes colu | Excel Discussion (Misc queries) | |||
automatic color change in cells using a drop down list | Excel Worksheet Functions | |||
Automatic Change of X-axis | Charts and Charting in Excel | |||
Is there an automatic way to change numbers such as 12312001 t | Excel Discussion (Misc queries) |