| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Odd Conditional Formatting
I was curious whether or not I could conditionally format a cell if it
contains part of what I'm wanting it to look for. In other words, my cells contain either "W 14" or DDD 12", for example. I just need the cells to change color based on whether or not it contains the "W" or the "DDD" and not the number that follows. How do I do this? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Odd Conditional Formatting
try:
FormulaIs: =OR(ISNUMBER(FIND("W",A1)),ISNUMBER(FIND("DDD",A1) )) "mailrail" wrote: I was curious whether or not I could conditionally format a cell if it contains part of what I'm wanting it to look for. In other words, my cells contain either "W 14" or DDD 12", for example. I just need the cells to change color based on whether or not it contains the "W" or the "DDD" and not the number that follows. How do I do this? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Odd Conditional Formatting
You only need to test the first character:
Format Conditional formatting... Formula Is =left(a1,1)="W" and pick the format Same for DDD -- Gary''s Student - gsnu200743 "mailrail" wrote: I was curious whether or not I could conditionally format a cell if it contains part of what I'm wanting it to look for. In other words, my cells contain either "W 14" or DDD 12", for example. I just need the cells to change color based on whether or not it contains the "W" or the "DDD" and not the number that follows. How do I do this? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Odd Conditional Formatting
Using A1:
formula is: =or(left(a1,2)="w ",left(a1,4)="ddd ") mailrail wrote: I was curious whether or not I could conditionally format a cell if it contains part of what I'm wanting it to look for. In other words, my cells contain either "W 14" or DDD 12", for example. I just need the cells to change color based on whether or not it contains the "W" or the "DDD" and not the number that follows. How do I do this? -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Odd Conditional Formatting
None of these worked for me. I pasted each formula into the box after
"formula is" and it did not conditionally format the cell. What could I be doing wrong? "mailrail" wrote: I was curious whether or not I could conditionally format a cell if it contains part of what I'm wanting it to look for. In other words, my cells contain either "W 14" or DDD 12", for example. I just need the cells to change color based on whether or not it contains the "W" or the "DDD" and not the number that follows. How do I do this? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Odd Conditional Formatting
So are you telling us that
=OR(ISNUMBER(FIND("W",A1)),ISNUMBER(FIND("DDD",A1) )) didn't give you the conditional format for cells that contained W or DDD? Are you sure that you specified an appropriate format in the CF? If you paste that formula into a separate cell does it give a TRUE or FALSE? Are you sure that you've got the formula that you think you have in CF? It does have a tendency to throw in unwanted quote marks or to convert from relative to absolute addressing. Are you sure that the formula refers to the appropriate cell? -- David Biddulph "mailrail" wrote in message ... None of these worked for me. I pasted each formula into the box after "formula is" and it did not conditionally format the cell. What could I be doing wrong? "mailrail" wrote: I was curious whether or not I could conditionally format a cell if it contains part of what I'm wanting it to look for. In other words, my cells contain either "W 14" or DDD 12", for example. I just need the cells to change color based on whether or not it contains the "W" or the "DDD" and not the number that follows. How do I do this? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Odd Conditional Formatting
Each of the formulas was written for A1.
If you were using a different cell, you'll have to change the formula to use that address. mailrail wrote: None of these worked for me. I pasted each formula into the box after "formula is" and it did not conditionally format the cell. What could I be doing wrong? "mailrail" wrote: I was curious whether or not I could conditionally format a cell if it contains part of what I'm wanting it to look for. In other words, my cells contain either "W 14" or DDD 12", for example. I just need the cells to change color based on whether or not it contains the "W" or the "DDD" and not the number that follows. How do I do this? -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Odd Conditional Formatting
i highlighted ALL cells to conditionally format.
"mailrail" wrote: I was curious whether or not I could conditionally format a cell if it contains part of what I'm wanting it to look for. In other words, my cells contain either "W 14" or DDD 12", for example. I just need the cells to change color based on whether or not it contains the "W" or the "DDD" and not the number that follows. How do I do this? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Odd Conditional Formatting
What was the address of the activecell?
What was the formula you pasted? What was in that cell? mailrail wrote: i highlighted ALL cells to conditionally format. "mailrail" wrote: I was curious whether or not I could conditionally format a cell if it contains part of what I'm wanting it to look for. In other words, my cells contain either "W 14" or DDD 12", for example. I just need the cells to change color based on whether or not it contains the "W" or the "DDD" and not the number that follows. How do I do this? -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Odd Conditional Formatting
I'm highlighting a whole range of cells and trying to get that conditional
format into each one with the same basic formula. Will that work or do I have to write an individual formula for each of those 100 or so cells? "Dave Peterson" wrote: What was the address of the activecell? What was the formula you pasted? What was in that cell? mailrail wrote: i highlighted ALL cells to conditionally format. "mailrail" wrote: I was curious whether or not I could conditionally format a cell if it contains part of what I'm wanting it to look for. In other words, my cells contain either "W 14" or DDD 12", for example. I just need the cells to change color based on whether or not it contains the "W" or the "DDD" and not the number that follows. How do I do this? -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Odd Conditional Formatting
You have to use the activecell in the selected area in your formula. If you
write the formula correctly, then excel will adjust for all the other cells in that selection. What was the address of the activecell? What was the formula you pasted? What was in that cell? If you answer those questions, you may get better responses. mailrail wrote: I'm highlighting a whole range of cells and trying to get that conditional format into each one with the same basic formula. Will that work or do I have to write an individual formula for each of those 100 or so cells? "Dave Peterson" wrote: What was the address of the activecell? What was the formula you pasted? What was in that cell? mailrail wrote: i highlighted ALL cells to conditionally format. "mailrail" wrote: I was curious whether or not I could conditionally format a cell if it contains part of what I'm wanting it to look for. In other words, my cells contain either "W 14" or DDD 12", for example. I just need the cells to change color based on whether or not it contains the "W" or the "DDD" and not the number that follows. How do I do this? -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Odd Conditional Formatting
The active cell when you select a range of cells is the cell that comes up
in the name box above the header of column A. For instance if you click in D7 and hold down the mouse button and select D7:D20 then D7 is the active cell and you obviously have to replace any reference to A1 in the formulas that were given to you with D7 -- Regards, Peo Sjoblom "mailrail" wrote in message ... I'm highlighting a whole range of cells and trying to get that conditional format into each one with the same basic formula. Will that work or do I have to write an individual formula for each of those 100 or so cells? "Dave Peterson" wrote: What was the address of the activecell? What was the formula you pasted? What was in that cell? mailrail wrote: i highlighted ALL cells to conditionally format. "mailrail" wrote: I was curious whether or not I could conditionally format a cell if it contains part of what I'm wanting it to look for. In other words, my cells contain either "W 14" or DDD 12", for example. I just need the cells to change color based on whether or not it contains the "W" or the "DDD" and not the number that follows. How do I do this? -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
| conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
| Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
| Conditional Formatting? | Excel Worksheet Functions | |||
| Conditional tab formatting? | Excel Discussion (Misc queries) | |||
Linear Mode
