Select formula & Constant (=A1*10) - ONLY??

[Rasec]

Can I ask excel to select only cells with a formula and a constant ONLY??
A B

<TABLE style="WIDTH: 213pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=284><COLGROUP><COL style="WIDTH: 117pt; mso-width-source: userset; mso-width-alt: 2852" width=156><COL style="WIDTH: 96pt" width=128><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffff99; WIDTH: 117pt; HEIGHT: 15pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=156>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 96pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=128>2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffff99; WIDTH: 117pt; HEIGHT: 15pt; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=156>=A1+7*320</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>=B1+C1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20>=A1+B3*Sheet2!A1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20>=IF(A2=A1,"","YOOO")</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR></TBODY></TABLE>

** I want excel to highlight =A1+7*320 only** and leave =b1+c1 untouched?

 

[Robert Mika]

Hi,
Could yoube more specific?
Do you want to extract this part from the actual formula?

 

[Ron Coderre]

I'm fairly certain you'd need to run a VBA pattern-matching macro to select formula cells that include numerics.

 

[Rasec]

Hi,
Could yoube more specific?
Do you want to extract this part from the actual formula?

Mr. Mika,

I do not want to extract anything from the 25,000 cells I have to look at. All I want excel to do is hight light in a yellow background and red font all the cells that contain a formula (logical inluded) and a constant number.

Cells that only contain a formula (includes logics) I want excel to leave with a white background and back font (the usual default color in excel)

Does this explain it betteR?

 

[Rasec]

I'm fairly certain you'd need to run a VBA pattern-matching macro to select formula cells that include numerics.

I don't know VBA. Any idea where I can find a code?

Thanks for your advice!

 

[mikerickson]

=1+2
=MAX(3, A1)
=MAX(3, A1:A2) +4
=A2 + 4*SIN(2)
=MAX(3, A1:A2)
=SIN(PI()*A1)
=FIND(A1&".", ".")

Which of those do you want highlighted?
and why

How do you want Names handled? Does it depend on how the name is defined?

 

[Rasec]

=1+2
=MAX(3, A1)
=MAX(3, A1:A2) +4
=A2 + 4*SIN(2)
=MAX(3, A1:A2)
=SIN(PI()*A1)

Which of those do you want highlighted?
and why

=MAX(3, A1) - This is a formula with a constant build-in. A person did not hardcode the 3. Leave blank (white background black font)

=MAX(3, A1:A2) +4 - this one should be highligted in light yellow and red font because the + 4 is a constant that someone had to manually input

=A2 + 4*SIN(2) - this one should be highligted in light yellow and red font because the + 4 is a constant that someone had to manually input. My model does not have any reference to SIN, COS...etc. it is a 40 tab financial model

=MAX(3, A1:A2) - this is a complete formula. No hardcoded numbers. Leave blank (white background black font)

=SIN(PI()*A1) - this is a complete formula. No hardcoded numbers. Leave blank (white background black font)


Thank you so much for taking the time to help me out! I'm sure your good deed will come back to ya!

 

[mikerickson]

So =SUM(4, A2) and =4 + A2 are to be handled differently?

 

[Rasec]

So =SUM(4, A2) and =4 + A2 are to be handled differently?

In this case they would be handled the same since the 4 on both formulas are numbers that are not being linked to another cell.

Im sorry if I cannot be more specific.

Thank you for your help.

 

[mikerickson]

Hmmm... I thought I had what you needed, but

Why is SUM(4, A2) different from MAX(3, A1)?

Here is the formula I thought would work.

Function hasMixedTerms(formulaSource As Variant) As Boolean
    Dim strFormula As String
    
    If TypeName(formulaSource) = "Range" Then
        strFormula = formulaSource.Cells(1, 1).Formula
    Else
        strFormula = CStr(formulaSource)
    End If
    
    Dim flag As Boolean
    Dim i As Long
    Dim Terms As Variant
    If strFormula Like "=*" Then strFormula = Mid(strFormula, 2)
    strFormula = Application.Substitute(strFormula, "-", "+")
    strFormula = Application.Substitute(strFormula, "*", "+")
    strFormula = Application.Substitute(strFormula, "/", "+")
    strFormula = Application.Substitute(strFormula, "^", "+")
    strFormula = Application.Substitute(strFormula, "(", vbNullString)
    strFormula = Application.Substitute(strFormula, ")", vbNullString)
    Terms = Split(strFormula, "+")
    If UBound(Terms) = 0 Then
        hasMixedTerms = False
    Else
        flag = IsNumeric(Terms(0))
        For i = 1 To UBound(Terms)
            If flag Xor IsNumeric(Terms(i)) Then hasMixedTerms = True: Exit Function
        Next i
    End If
End Function