Color non empty cell of an entire column

Question

I would like to color the non empty cell of a column in a sheet. I would prefer to do it with conditionnal formating

When i set it up without vba it looks like :

I tried

.Columns(7).FormatConditions.Add(Type:=xlExpression,_
     Formula1:="=G:G<> """"").Interior.Color = MyFormating

The range is ok, but the formula doesn't works

Answer 1

Try: .Columns(7).FormatConditions.Add(Type:=xlExpression,_ Formula1:="=G1<> """"").Interior.Color = MyFormating

A brief explanation:

Conditional formatting will assume the formula provided applies to the first cell (top-left) in the range the condition is applied to. Excel will then adjust the formula for each cell. (as long as the references are not absolute)

So if your condition applies to range G:G, Excel will use the conditional formulas as follows:

For G1: .Columns(7).FormatConditions.Add(Type:=xlExpression,_ Formula1:="=G1<> """"").Interior.Color = MyFormating

For G2: .Columns(7).FormatConditions.Add(Type:=xlExpression,_ Formula1:="=G2<> """"").Interior.Color = MyFormating

For G3: .Columns(7).FormatConditions.Add(Type:=xlExpression,_ Formula1:="=G3<> """"").Interior.Color = MyFormating

and so on..

This applies in both directions, so that if you applied the condition on multiple columns (say G:L), you would get:

For H1: .Columns(7).FormatConditions.Add(Type:=xlExpression,_ Formula1:="=H1<> """"").Interior.Color = MyFormating

For I1: .Columns(7).FormatConditions.Add(Type:=xlExpression,_ Formula1:="=I1<> """"").Interior.Color = MyFormating

and so on