Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Conditional Formatting in excel Based - Cells with and without Formulas

Posted on 2011-03-22
4
Medium Priority
?
230 Views
Last Modified: 2012-05-11
1. i need a CF to color cells without formulas  column 1
2. i need a CF to color cells with formulas       column 2
0
Comment
Question by:ansonindustries
  • 2
4 Comments
 
LVL 19

Expert Comment

by:Raheman M. Abdul
ID: 35193744
0
 
LVL 33

Accepted Solution

by:
jppinto earned 2000 total points
ID: 35193776
I've attached a working sample. I've used a UDF in VBA:

Function CellFormula(cel As Range) As String
    CellFormula = cel.Formula
End Function

This function will return the formula on a cell. Using this function on a Conditional Formatting rule, I check if the cell has a function (checking if it begins with a =) and on the first column, if it doesn't have a formula, I change the color (to yellow) on the second column, I check if it has a formula and if true change the color to orange.

Hope this is clear.

jppinto
ConditionalFormatting.xlsm
Capture.JPG
0
 
LVL 19

Expert Comment

by:Raheman M. Abdul
ID: 35193814
Replace with =NOT(IsFormula($B$3)) for the opposite function. ( is not the formula) select desired color
0
 

Author Closing Comment

by:ansonindustries
ID: 35194120
Thanks works great!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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 will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

824 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