# Solved: Excel VBA -> Conditional Formatting



## computerman29642 (Dec 4, 2007)

I am trying to apply condtional formatting through VBA. Here is the code I am playing with at the moment.


```
Range("D2:D" & LastRow).FormatConditions.Add Type:=xlExpression, Formula1:="=AND(OR(D2<>0,D2<>""""),A2<>123,A2<>987,A2<>852,A2<>753,A2<>""User"")"
    Range("D2:D" & LastRow).FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
    Range("D2:D" & LastRow).FormatConditions(1).Interior.ColorIndex = 36
    Range("D2:D" & LastRow).FormatConditions(1).Interior.TintAndShade = 0
```
When I test the conditional formatting on the worksheet itself, all appears to be working. However, when I try to apply the conditional formatting through VBA it is not working at all.

Can someone please point me in the right direction? I believe I am missing something simple, but I have not been able to place my finger on what I am doing wrong.


----------



## Jimmy the Hand (Jul 28, 2006)

Not sure what you mean by "not working at all".
Nevertheless I played around with your code, and made an observetion. In my experience, cell references in the format condition formula get shifted, depending on the currently selected cell.
For example:
Select A1, run the code, and the format condition formula in D2 will be 
=AND(OR(G3<>0;G3<>"");D3<>123;D3<>987;D3<>852;D3<>753;D3<>"User")
Select F6, run the code, and the format condition formula in D2 will be 
=AND(OR(B65534<>0;B65534<>"");IU65534<>123;IU65534<>987;IU65534<>852;IU65534<>753;IU65534<>"User")

Running the code only created the right format condition formula when the selected cell was D2. So I would suggest modifying your code this way:


```
With Range("D2:D" & lastrow)
    .Select
    .FormatConditions.Delete               'remove this line if you wish
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(OR(D2<>0;D2<>"""");A2<>123;A2<>987;A2<>852;A2<>753;A2<>""User"")"
    .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
    .FormatConditions(1).Interior.ColorIndex = 36
    .FormatConditions(1).Interior.TintAndShade = 0
End With
```
Note:
I have Excel 2003, and the *TintAndShade* property (last code line) was unknown for my system, so I suppose you are using 2007. I'm not sure my tip is valid on that system.


----------



## computerman29642 (Dec 4, 2007)

Jimmy, why did you use a semi-colon to separate the conditions instead of a comma?


The OR Statement does not appear to work. The cells that contain a 0 are still being highlighted. Do I need the OR Statement or should I place that criteria in with AND Statement?


----------



## Jimmy the Hand (Jul 28, 2006)

Sorry for the semicolons. It's just the the regional settings in Hungarian Excel. I changed the function names to English but forgot to replace the semicolons.

Let's test the expression *OR(D2<>0,D2<>"")* 
If *D2=0* then 
*D2<>0* is false but
*D2<>""* is true. So the whole expression results true. That's why cells that contain a 0 are being highlighted.

If you want to exclude both zeros and empty cells, you need, as you guessed right, to put both conditions into the AND part:

```
Range("D2:D" & LastRow).FormatConditions.Add Type:=xlExpression, Formula1:="=AND(D2<>0,D2<>"""",A2<>123,A2<>987,A2<>852,A2<>753,A2<>""User"")"
```
Or you can use the VALUE function, like this:

```
Range("D2:D" & LastRow).FormatConditions.Add Type:=xlExpression, Formula1:="=AND(VALUE(D2)<>0,A2<>123,A2<>987,A2<>852,A2<>753,A2<>""User"")"
```


----------



## computerman29642 (Dec 4, 2007)

That makes complete sense. I did not think of it that a way. Thanks Jimmy.  :up:

I assume that the VALUE function takes care of empty cells as well?


----------



## Jimmy the Hand (Jul 28, 2006)

Welcome 
And yes, you assume right.


----------



## computerman29642 (Dec 4, 2007)

I think I will try the code with the VALUE function. Thanks again Jimmy. You have been a big help.


----------

