Conditonal Formatting caaling modules

Folks,
Open the attached file and select the tab that is "UserDefineDetectCells"and colored red.
What you will see is a bordered range from A2:C10 with no visible data.
Click on "Display Data" command button and data appears.
The purpose for this is to identify only those cells that have formulas and there are two: C3 and C7 using conditonal formatting.
I use two macros. The first is:
Function HF(rng) As Boolean
HF = rng.HasFormula
End Function

Open in new window

I will use HF in my other macro called "DetectCellsFormulas" that uses conditional formatting. The formula there is =HF(A2)
MY problem is that cells with formulas are not being formatted?
CondFormatMacro.xlsm
Frank FreeseAsked:
Who is Participating?
 
FaustulusConnect With a Mentor Commented:
Well, you and I aren't the only ones having this problem. The method works manually but not when encoded. Here is an identical one - unsolved.
I recommend this method instead and have encoded the idea for you in the attached workbook. Note that both, the named range and all previously existing conditional formats are deleted when the code starts up.

Observe that the FormatConditions object's Formula1 property must include the leading "=" sign. You had omitted that, but adding it didn't make your code work any better.
EXX-131130-CF-Cells-With-Formula.xlsm
0
 
andrew_manConnect With a Mentor Commented:
Dear fh_freese,

See enclosed is my answer.  By the way, the statement of  Faustulus  is not true at all.  I used since 1994.  It is possible coding for conditional formatting, but it cannot apply to user defined function.

Please notes.

Andrew Man from Hong Kong
CondFormatMacro.xlsm
0
 
Frank FreeseAuthor Commented:
Andrew,
Question: I've not been able to locate the formula = ISF that you used in his conditional formatting formula.
Both solutions are workable, so I'm grateful there.
Points to the two of you will be distributed equally. Since I have two workable solutions I beleieve that to be fair. Thank you
0
 
Frank FreeseAuthor Commented:
Ok...I found the ISF as a range, Andrew.
0
 
Frank FreeseAuthor Commented:
thanks to all. I am grateful for EE's continued support.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.