Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Conditonal Formatting caaling modules

Posted on 2013-11-30
5
Medium Priority
?
167 Views
Last Modified: 2013-12-01
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
0
Comment
Question by:Frank Freese
  • 3
5 Comments
 
LVL 14

Accepted Solution

by:
Faustulus earned 1000 total points
ID: 39687713
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
 
LVL 4

Assisted Solution

by:andrew_man
andrew_man earned 1000 total points
ID: 39688151
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
 

Author Comment

by:Frank Freese
ID: 39688342
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
 

Author Comment

by:Frank Freese
ID: 39688596
Ok...I found the ISF as a range, Andrew.
0
 

Author Closing Comment

by:Frank Freese
ID: 39688599
thanks to all. I am grateful for EE's continued support.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question