You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Conditional Formatting

Advertisement

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!

KK

ANSWER: KK,

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

Sub setCondFormat()

Range("B3").Select

With Range("B3:H63")

.FormatConditions.Add Type:=xlExpression, Formula1:= _

"=IF($D3="""",FALSE,IF($F3>=$E3,TRUE,FALSE))"

With .FormatConditions(.FormatConditions.Count)

.SetFirstPriority

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

=Trunc(rand()*20+1)

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.

--

Regards,

Tom Ogilvy

--

Regards,

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.

Regards.

KK

KK

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.

--

Regards,

Tom Ogilvy

- View Follow-Ups
- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | Thanks a lot. |

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

Excel

Answers by Expert:

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. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.