Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Conditonal Formatting caaling modules

Posted on 2013-11-30
5
Medium Priority
?
163 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

604 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