code error assistance

[Rebellion34]

all, I have a code that worked until I added an exception to it the code is:
Sub Macro4()
'
' Macro4 Macro
'

'
Range("I3:I43").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=if(LEN(TRIM(I3))>0"[red]-if(I:I,"order")-if(I:I,"dialogue")[/red]
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249946592608417
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
can you show me where i went wrong in the argument so that it will work please? the aded exception is in red.

thanks in advance for any help.

Rob

 

[SkipVought]

please post the FORMULA that you have in Conditional Formating that actually WORKS. I do not want to see any VBA!

Also post the CELL ADDRESS that this CF is coded in.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Rebellion34]

Skip,
not sure how to get the full formula i used CF and chose the following options " format only cells with no blanks" which gave this formula:=LEN(TRIM(I3))>0, then chose "format fill with med grey color" then gave it a cell range of I3:I43 which gave me :=$I$3:$I$43 hope this helps.

thanks

 

[SkipVought]

And this gave you the CF results that you want over the range of rows 3:43?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Rebellion34]

yes sir basically to shade any cell with data within the range column I rows 3-43, what i need is to allow that to happen with the exception if any cell contains "Dialouge" or "Order" which i need to be left without a fill color and any blank cell to be left without fill.

 

[Rebellion34]

Skip, actually this works on any cell in column I but that is not a big issue as i the cels i only use upto row 43 and if the cell is blank it does not fill.

 

[SkipVought]

    Range("I3:I43").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM(I3))>0"
...

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Rebellion34]

how do i get this code to ignore specific words and yet fill any other word as this is where i am having the hang up?

 

[SkipVought]

Please explain EXACTLY and SPECIFICALLY what you want to happen.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

maybe this???

    Range("I3:I43").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(LEN(TRIM(I3))>0,I3<>""order"",I3<>""dialog"")"
...

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Rebellion34]

Skip I just tried that code and it is still filling the cells that contain those 2 words

 

[SkipVought]

Is that 'CONTAIN' or the EQUAL?

It will not set the CF if the value in column I, is EQUAL TO order or dialog, which is what I deduced from your original post, as you NEVER actually stated exacly WHAT your requirements are.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Rebellion34]

Skip,

what i need is a code or formula that will fill any cell with any color in column I with any data inside it with the exception of the 2 specific words dialog and order which needs to remain unfilled, the current code allows and cell that cantains any word including those key words. an example would be:
I
1 |blue |
2 |dialog |
3 |1 count|
4 |speed |
5 |order |
in column I rows 1,3,4 would fill with color but 2 and 5 would remail clear. does this help?
thanks

 

[SkipVought]

...and the above solution does NOT work?

Works for me.

Do you have any leading/traling spaces?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Rebellion34]

Skip,

you are correct I think I misses something when copying this last time. thanks again for coming through you have helped me out a lot and to learn a lot i really appreciate all the time you spend fixing and writing codes for us novices.