CODEWITHSUNDEEP
excelvbaconditional-formatting
Foxhunt
Foxhunt

Reputation: 870

Color non empty cell of an entire column

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 : wanted formating

I tried

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

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

Upvotes: 0

Views: 51

Answers (1)

cybernetic.nomad
cybernetic.nomad

Reputation: 6368

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

Upvotes: 1

Related Questions