# Thread: conditional format help

1. ## conditional format help

Formula is:
=IF(B2="","",IF(ISERROR(DATEDIF(B2,C2,"d")),"no end
date",DATEDIF(B2,C2,"d")))

I want to Conditional format so when:
"D" has a # return it is yellow
DATEDIF(B2,C2,"d"
"D" returns "" it is white
IF(B6="",""
"D" returns "no end date" it is green
IF(ISERROR(DATEDIF(B2,C2,"d")),"no end date"

My CF is:
Cell value >0 = yellow
Cell value ="" = no color
Cell value ="string" = green

Results = All cells are yellow.

CF works correctly if I rearrange the order to:

Cell value ="" = no color
Cell value ="string" = green
Cell value >0 = yellow

I assume that the order determines priority, but why would the >0
affect, or interfere with, the strings/null? Is a string or null
string < or >0? Does it have any numeric value?

How can I change "Cell value >0" so I can place it first and have
this work. Stated another way, what can I use so it would not
interfere with the other two?

Question #2 - Column E
I have a formula which returns a string "no \$C\$5" if B5 has a date
but C5 is empty. It changes as the rows change, that is the next row
would say "no \$C\$6" if B6 has a date but C6 does not.

=IF(B43="","",IF(ISERROR(DATEDIF(B43,B44,"d")),"no "&" "&CELL("address",B44),DATEDIF(B43,B44,"d")))

How can I CF this return without having to do each row separately
using the specific string return [no \$C\$5, no \$C\$6, etc] or without
using ">than \$C\$1" ?
I want to know other ways to do it maybe using a variable or other means to change the number(x) [\$C\$x] with corresponding change of the row.

See attached sample.

2. Will this work for Column D? It uses a CF test of isnumber for first condition.
If yes, just use the Format Painter for any additional rows of data in column D.

3. ## The Following User Says Thank You to tfspry For This Useful Post:

skipro (2011-04-02)

4. This might work for Col D and Col E

Let me know

PS: Copy and Paste should work in both columns.

5. ## The Following User Says Thank You to tfspry For This Useful Post:

skipro (2011-04-02)

6. tfspry,
Thanks.
First suggestion works great, second one works but will color all cells with no number. I want only the cell with the string return colored.
Can a variable or a wildcard be used in CF? Can it be done in this case?

7. Hi skipro- Will the attached do what is needed?

HTH

8. Yes, thanks.
I am still looking for a more direct solution if anyone knows how. I am curious if a variable, wildcard or other method can be used in CF.

9. I am still looking for a more direct solution if anyone knows how.
I don't understand what you mean by "more direct solution". The solutions provided seemed direct to me. What do you consider not direct about them?

I am curious if a variable, wildcard or other method can be used in CF.
Variables can be used, wildcards can be used, and there are many other methods. What you asked for did not require them. Do you have a simple example that you think requires a wildcard or a variable?

Steve

10. SDCKPR,
Thanks for responding.
Answer to your 2nd question:
Although my sample does not "require" the use of variables, wildcards or a different approach, I would be interested to see how they can be used for future reference. Can you, or someone else, show me how a variable, a wildcard and other approaches would work either in my sample, in general or lead me to references.

Answer to your 1st question:
I am only referring to Column "E" in my sample.
If, as you state, variables, etc can be used, I assume this would be more direct, efficient and customizable.

I see the offered solutions as functional for this sample but indirect and less efficient. A more direct solution would state:

1) If numbers exist, do "A"
2) If any variable of string \$C\$x[x as number] do "B"

and do so with the least rules, be specific to this string needs and be available as a standard which would be more universal and useable if the need is reasonably modified.
Rule 1= {Isnumber} seems to be an effective approach.
Rule 3= {Cell Value Is Not equal to 0} works but is an indirect approach to the
string issue, an elimination approach and general.
Rule 2= {Formula is =Bx=""} again works, but is only needed to override Rule 3 because of Rule 3's lack of specificity.

To me, this is less direct or definitive and less efficient.
I recognize I did not state this originally and my choice of words describing
my goals was not accurately descriptive. I am not suggesting the replies are
inadequate for my sample, but rather I am seeking another and, as I interpret it, [here I go again], more direct approach.

I am seeking
A) Using only 2 rules which would be simpler and saving the 3rd, for future
use, avoiding Excel's limitation. [I am using Excel 2003]
B) [referenceing "2)" above]
Only the specified string [\$C\$x] or its cousins[modified] would {do "B"}.
The solutions offered are not string [or it's variations] specific. If future
needs required another string to also be present, the solution would not
perform as needed.

That is what I meant as more direct or definitive. I am seeking an approach I
can use for future unknown but similar needs which through the use of
variables, wildcards or other methods/functions would expand the possibilities while being as direct/efficient as possible.

I use this forum not only to solve a specific issue, but to also learn. That
is why I asked the questions in the original post and why I am pursuing this
further and asked the question in the April 2 reply.
I hope I have clearly stated my objectives.

11. Originally Posted by skipro
...........
I see the offered solutions as functional for this sample but indirect and less efficient. A more direct solution would state:

1) If numbers exist, do "A"
2) If any variable of string \$C\$x[x as number] do "B"
..........

I am seeking
A) Using only 2 rules which would be simpler and saving the 3rd, for future
use, avoiding Excel's limitation. [I am using Excel 2003]
The attached will work with just 2 CF's.

12. There are several optional Conditional Formats that can be used.

Using file in Post #9 for example: Condition #2 could be ...
Formula is =left(E6,1)>="a"

Just need some examples of variations you would like to use.

13. ## The Following User Says Thank You to tfspry For This Useful Post:

skipro (2011-04-04)

14. Tfspry,
Thanks. Both work fine and taught me another approach to use. I appreciate the 2 variations as it shows me how the concept can be manipulated.
The first formula is more specific than the second one and has less chance of overlap with other string returns, which is my goal. I modified it to be even more specific and essentially eliminate the chance of overlap with another string return with:
=LEFT(E5,6)="no \$C\$"
This is a direct approach I have been seeking. Although it does not actually use a variable or wildcard, it does conceptually. To me, its specificity makes it more elequent.

Can characters be skipped?
Ex: Can you use character 3-5, skipping characters 1&2?
Can you suggest any other approaches? Even suggestions to other functions would be helpful.

15. Originally Posted by skipro

Can characters be skipped?
Ex: Can you use character 3-5, skipping characters 1&2?
Can you suggest any other approaches? Even suggestions to other functions would be helpful.
To get characters 3-5, you could use =MID(E5,3,3)

Here is a Microsoft site for some Excel 2003 Functions.....
List of Excel functions (by category)

Among others, take a look at the Text functions.

16. Tfspry,
Thanks.

17. Another way to use wildcards with cond formatting is to use countif, for example:
=COUNTIF(e5,"=*He*lo*")>0

will be true for cells with "He" in it followed by "lo" elsewhere in the cell...

Steve

18. ## The Following User Says Thank You to sdckapr For This Useful Post:

skipro (2011-04-06)

19. Originally Posted by skipro
Tfspry,
Thanks.
skipro - Your welcome. Glad we could help.

There are so many possible functions/combinations that can be used in Conditional Format. Feel free to ask for more solutions for your ideas.

Here is another Conditional Format (For condition #2) to test for characters in position 4-6 (\$C\$ ) in Col E......
=MID(E5,4,3)="\$C\$"

20. ## The Following User Says Thank You to tfspry For This Useful Post:

skipro (2011-04-06)

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•