You are here:

Excel/Conditional Formatting


QUESTION: Hello Tom,

Your response:

"I did find some code that entered a formula using relative references and it worked"

Can you provide your code to me?

Thanks and regards!



this applies formatting to  B3:H63 and colors each row where column F is greater than or equal to column E.

Sub setCondFormat()
   With Range("B3:H63")
       .FormatConditions.Add Type:=xlExpression, Formula1:= _
       With .FormatConditions(.FormatConditions.Count)
         With .Interior
         .PatternColorIndex = xlAutomatic
         .Color = 5287936
         .TintAndShade = 0
         End With
       End With
   End With
End Sub

that worked for me.  Note that it select B3 as the first command - that handles the relative cell reference I spoke of in my first post.  But I tried that with your code and it didn't work. I always fill my range with random numbers such as


so I can hit F9 and the values of the cells will change.

Just a few words about your code/situation:

So I did that with your code in column B and C  (C was =if(rand()<.6,101,10)

I found that most of the time it either shaded nothing or it shaded something appearing random.  However, when it did shade a value next to 101 it was always the max value.  Unfortunately, when I read the formulas, the group always displayed the same formulas.  And when I entered each row individually with VBA, the behavior was not altered in terms of not working as described above.  But checking the formulas by row, they all looked good.

Tom Ogilvy

Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: Hello Tom,

I also tried to modifiy the conditional formatting formula in Excel manually after running my macro. If I press "CTRL+SHIFT+ENTER", the formula is not displayed "{}", but it can highlight properly.

Unfortunately, the marco recorder cannot record "CTRL+SHIFT+ENTER", so I think VBA cannot handle array formula.

Thanks for your reply.




little added info at the bottom:

VBA can handle array formula entry with the FormulaArray property.  (This is a property of a range object).  It is not available in defined names and conditional formatting, but as I say below it isn't necessary.  

For the areas of defined names and conditional formatting, formulas are not array entered.  If an array interpretation needs to be made, the two areas seem to do it "magically".   But perhaps "magically" works most of the time but not all times - I can't really say how the formula is parsed and interpreted internal to conditional formatting and defined names.  I just know that you do not explicitly indicate the formula is an array formula when you enter the formula in those two cases.  (as you say, it is not displayed with {}).

I found a post that said if the formula is entered in R1C1 notation that it will work with VBA.   I tried that both with the sheet in A1 and in R1C1 mode and had exactly the same problem.  Just to add however, if after the code enters the formula (which looked perfect), I went into the conditional formatting manager and selected this format, hit edit (did nothing),  then OK and then Apply as soon as I did that, I could see the conditional formatting update correctly in the sheet.  I then clicked OK to exit and the formula continued to work.  This was in every case.  So the code is putting in the correct formula, but to get it to work, one must simulate that it had been entered manually.  I don't believe there is anyway to do that with code except perhaps by using send keys, but I believe that would be pretty flakey even if possible.

Very strange.

Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2017 All rights reserved.