How to apply the conditional formatting to multiple columns using excel macro

Question

I created a conditional formatting to change the color based on value compare for one cell, but I don't know how to apply this formatting for the whole column and even other columns? (do I have to use for loop to set the formatting for all the cells?)

'add conditionalFormating for one cell.
 Set rngCell = Cells(6, 7)
 Set objCF = rngCell.FormatConditions.Add _
            (Type:=xlExpression, _
            Formula1:="=" & rngCell.Address & " > " & rngCell.offset(, -3).Address)
'set formats for new CF

With objCF
    .Font.ColorIndex = 26
    .Interior.ColorIndex = 19
End With

Thanks in advance

I tried, but it doesn't work. it only record the following script:<br>Sub Macro2() ' ' Macro2 Macro ' ' Range("B1").Select End Sub — David Kin
– David Kin, Commented Apr 24, 2012 at 10:42
How are you recording the macro? I just tested in Excel 2007 and 2010 and it records the macro perfectly. — Siddharth Rout
– Siddharth Rout, Commented Apr 24, 2012 at 11:07

Ed Bolton · Accepted Answer · 2012-04-24 11:01:07Z

1

Unfortunately, Excel 2007/2010 don't have as extensive a macro record function as earlier versions

If you are referencing other cells when applying conditional formatting, a good method is to apply it to one cell (which you know how to do), and then copy the formatting to the rest of the column; if you are filling down a column, this should not cause you to lose other cell formats you may wish to keep

So I would start your code with

rngCell.FormatConditions.Delete

Then once the format is added, you can simply use:

rngCell.Copy
rngOut.PasteSpecial xlPasteFormats

, where rngOut is defined as starting with rngCell and filling down to the last row in the table

To apply to other columns, you would probably need a different formula as there are different offsets. To minimise the required code, you could always manually add the full set of formats/conditional formats you want to a hidden row just above the table's headers. Then you can copy all of these to your table using code of the form...

Range("A1:J1").Copy
Range("A3:J100").PasteSpecial xlPasteFormats

...if we assume your header gets pushed down to row 2 when you add the formatted row in row 1

Although I have referred to cell addresses in the above example, I would always recommend referring to range names and using cells notation e.g. Range("A1") is Cells(1, 1). The use of range names to define columns in tables is covered in more detail in this expert Excel video. The benefits are immense...if your header row is defined with a range name, you can insert a new row above your table without having to re-write any code

answered Apr 24, 2012 at 11:01

Ed Bolton

9466 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Kin Over a year ago

Excellent and professional answer! now I can apply this conditional formatting to the whole column.

David Kin Over a year ago

I have another question, do you know how to implement the formatConditions to change the cell color according to the cell value in another cell. my previous formatCondtions is not very correct, and I know I need to use xsCellValue type, but don't know how to implement the rule like : Cell value > $D5

Collectives™ on Stack Overflow

How to apply the conditional formatting to multiple columns using excel macro

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related