Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I am trying to create a conditional format that will look at 30 cells and if
any one of the cells matches the current cell it should change the color of the number. Example: Each cell contains the following number A B C D E 1 1 2 5 1 2 2 3 4 2 3 4 3 5 6 1 5 6 4 1 2 5 2 5 5 3 4 2 4 2 6 5 6 1 6 1 15 3 In cell A15 I would like a conditional format that would take the number in cell A15 and check to see if it is in the area of A1..E6. If there is one number that matches the number in A15 then I would like the number in A15 to change to a color, if not it remains unchanged. The numbers could be spread out throughout the spreadsheet so I would need to reference 30 specific cells not just the range. |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Try
=(COUNTIF($A$1:$E$6,A15))=1 as the formula in the FORMULA IS option of Conditional Formatting and choose the formatting you want You can enter it for any cell and then paint on other cells you want to highlight. "basic" wrote: I am trying to create a conditional format that will look at 30 cells and if any one of the cells matches the current cell it should change the color of the number. Example: Each cell contains the following number A B C D E 1 1 2 5 1 2 2 3 4 2 3 4 3 5 6 1 5 6 4 1 2 5 2 5 5 3 4 2 4 2 6 5 6 1 6 1 15 3 In cell A15 I would like a conditional format that would take the number in cell A15 and check to see if it is in the area of A1..E6. If there is one number that matches the number in A15 then I would like the number in A15 to change to a color, if not it remains unchanged. The numbers could be spread out throughout the spreadsheet so I would need to reference 30 specific cells not just the range. |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Select A15, Conditional Formatting and:
Formula Is: =COUNTIF($A$1:$E$6,A15) HTH Kostis Vezerides On Nov 12, 4:46*pm, basic wrote: I am trying to create a conditional format that will look at 30 cells and if any one of the cells matches the current cell it should change the color of the number. Example: * Each cell contains the following number * * * * * * *A * * * * * * B * * * * * * C * * * * * *D * * * * * *E *1 * * * * *1 * 2 * * * 5 * * * 1 * * * 2 *2 * * * * *3 * 4 * * * 2 * * * 3 * * * 4 *3 * * * * *5 * 6 * * * 1 * * * 5 * * * 6 *4 * * * * *1 * 2 * * * 5 * * * 2 * * * 5 *5 * * * * *3 * 4 * * * 2 * * * 4 * * * 2 *6 * * * * *5 * 6 * * * 1 * * * 6 * * * 1 15 * * * * 3 In cell A15 I would like a conditional format that would take the number in cell A15 and check to see if it is in the area of A1..E6. If there is one number that matches the number in A15 then I would like the number in A15 to change to a color, if not it remains unchanged. The numbers could be spread out throughout the spreadsheet so I would need to reference 30 specific cells not just the range. |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi,
To conditionally format your cell(s): 1. Select the cell you want to format 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =OR(A15=$A$1:$E$6) 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. If this helps, please click the Yes button Cheers, Shane Devenshire "basic" wrote: I am trying to create a conditional format that will look at 30 cells and if any one of the cells matches the current cell it should change the color of the number. Example: Each cell contains the following number A B C D E 1 1 2 5 1 2 2 3 4 2 3 4 3 5 6 1 5 6 4 1 2 5 2 5 5 3 4 2 4 2 6 5 6 1 6 1 15 3 In cell A15 I would like a conditional format that would take the number in cell A15 and check to see if it is in the area of A1..E6. If there is one number that matches the number in A15 then I would like the number in A15 to change to a color, if not it remains unchanged. The numbers could be spread out throughout the spreadsheet so I would need to reference 30 specific cells not just the range. |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thanks for your help. I have tried this and it works fine if your range is
all together, but my 30 numbers are all over the spreadsheet and can't be grouped together. I tried to adjusted the formula to address each individual cell and it worked fine until I hit around the 17th cell. It must have a max. on it. Any other ideas? "Shane Devenshire" wrote: Hi, To conditionally format your cell(s): 1. Select the cell you want to format 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =OR(A15=$A$1:$E$6) 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. If this helps, please click the Yes button Cheers, Shane Devenshire "basic" wrote: I am trying to create a conditional format that will look at 30 cells and if any one of the cells matches the current cell it should change the color of the number. Example: Each cell contains the following number A B C D E 1 1 2 5 1 2 2 3 4 2 3 4 3 5 6 1 5 6 4 1 2 5 2 5 5 3 4 2 4 2 6 5 6 1 6 1 15 3 In cell A15 I would like a conditional format that would take the number in cell A15 and check to see if it is in the area of A1..E6. If there is one number that matches the number in A15 then I would like the number in A15 to change to a color, if not it remains unchanged. The numbers could be spread out throughout the spreadsheet so I would need to reference 30 specific cells not just the range. |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
One way out is have a contiguous group of cells somewhere on your sheet and
enter the address of one of your separate cells in one of them, then refer that range in your conditional formula if you have cells A1, B5, D9,... then enter this =A1 =B5 =D9... in Z1, Z2, Z3... for example "basic" wrote: Thanks for your help. I have tried this and it works fine if your range is all together, but my 30 numbers are all over the spreadsheet and can't be grouped together. I tried to adjusted the formula to address each individual cell and it worked fine until I hit around the 17th cell. It must have a max. on it. Any other ideas? "Shane Devenshire" wrote: Hi, To conditionally format your cell(s): 1. Select the cell you want to format 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =OR(A15=$A$1:$E$6) 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. If this helps, please click the Yes button Cheers, Shane Devenshire "basic" wrote: I am trying to create a conditional format that will look at 30 cells and if any one of the cells matches the current cell it should change the color of the number. Example: Each cell contains the following number A B C D E 1 1 2 5 1 2 2 3 4 2 3 4 3 5 6 1 5 6 4 1 2 5 2 5 5 3 4 2 4 2 6 5 6 1 6 1 15 3 In cell A15 I would like a conditional format that would take the number in cell A15 and check to see if it is in the area of A1..E6. If there is one number that matches the number in A15 then I would like the number in A15 to change to a color, if not it remains unchanged. The numbers could be spread out throughout the spreadsheet so I would need to reference 30 specific cells not just the range. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Conditional formating based on other cells | Excel Worksheet Functions | |||
Conditional Formating based on a different cell | Excel Discussion (Misc queries) | |||
conditional formating cells i Excel based on other cells values | Excel Worksheet Functions | |||
Conditional Formating based on another cell | Excel Discussion (Misc queries) | |||
conditional formating - Highlighting text cells based on sales res | Excel Discussion (Misc queries) |