Excel 2010 Conditional Formatting based on formula in cell (not value in cell)

[slowjo1414]

Hello,

I am wondering if anyone can tell me how to make conditional formatting based on a formula in a cell, not the value of the cell?

To give some background:

I have a sheet that compares financial information of various companies (let's call it 'Sheet 1') using a vlookup to a sheet that pulls from a database (let's call it 'Sheet 2'). The last column in 'Sheet 1' is a column with my company's information. Most of the financial information for my company comes from a 'Sheet 3' that contains corporate model information. Some of the financial information on my company that is not found in 'Sheet 3' is pulled from the database 'Sheet 2' using a vlookup. I want to set up conditional formatting so that I can see which cells have been pulled from 'Sheet 2' (formula using a vlookup) and which cells have been pulled from 'Sheet 3' (just a direct reference to the exact cell).

If any one could give me some insight into how to solve this problem I would really appreciate it!

Thank you!!

 

[bluedragon2]

What logic are you using to determine which sheet to pull it from? Basically you would use the same logic in your CF formula.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[slowjo1414]

I'm not currently using any logic other than just searching manually for it. I thought about using a formula to determine where I pulled it from but since the row labels from one sheet refer to various metrics differently than the other (eg. "Cash Flow per Share/Trust Unit - Basic - calculated" on one sheet and "CF/Share" on the other) I can't think of what I would use other than pulling it manually.

 

[xlbo]

I think the only way you're going to be able to do this is via code - I don;t know of any way to interrogate a cell about it's formulaic contents without code

something like this on change event could work...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range
Set iSect = Intersect(Target, Columns("Z")) ' assumes col Z is the last column in your sheet
If Not iSect Is Nothing Then

    Dim lRow As Long
    lRow = Cells(Rows.Count, "Z").End(xlUp).Row
    
    For Each cel In Range("Z1:Z" & lRow)
        
        If InStr(1, cel.Formula, "VLOOKUP") > 0 Then
        
            'vlookup formula found
            cel.Interior.ColorIndex = 3
        Else
            
            If Left(cel.Formula, 1) = "=" Then
                
                cel.Interior.ColorIndex = 6
            Else
            
                cel.Interior.ColorIndex = 0
            End If
            
        End If
    Next
    
End If
End Sub

This will highlight formulas with VLOOKUP as red, other formulas as yellow and static values will be white

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Gruuuu]

Hit Ctrl+G (Goto)
Click Special...
Select Formula
Click OK
All cells with formulas are now selected.

Recording that action produced this code:

[tt]
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
[/tt]

If this must be done with VBA, this could be used, as it returns a Range object.

I should note also that the code will only "select" cells inside the parent range object. If I were to rewrite this code like this:

[tt]
Range("A:A").SpecialCells(xlCellTypeFormulas, 23).Interior.Color = RGB(255,255,0)
[/tt]

It would color all formula cells yellow in column A, and none from anywhere else. Likewise,

[tt]
Range("A:A").SpecialCells(xlCellTypeConstants, 23).Interior.Pattern = xlNone
[/tt]

will remove a color from all non-formula cells in column A, and none from anywhere else.

 

[mintjulep]

Possibly simplified approach.

Since native Conditional Formatting includes the ability to format based on formulas.

And the formulas that can be constructed can include functions, including UDFs.

So, simple UDF to return a cell's formula:

Public Function MyFormula(ByRef target As Range) As String
MyFormula = target.Formula
End Function

Then use that MyFormula in native conditional formatting.

=FIND("Sheet2",MyFormula(A4))>1

 

[xlbo]

Gruuu - that won;t work as OP needs to identify 2 different types of formula
Mintjulep I think takes this one!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[slowjo1414]

Awesome! Thank you very much for your suggestions. I'll try it out and see how it works.

Thanks again!