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?

 

[Rasec]

Thank you so much!!

In that case, both formulas are treated the same.

Thank again!

 

[mikerickson]

The code I posted does not flag =SUM(3, A1).

Does it meet your requirements? Are we done?

 

[Rasec]

The code I posted does not flag =SUM(3, A1).

Does it meet your requirements? Are we done?

I ran it but nothing happened.

I dont know why

i used a clean worksheet and made up some numbers , formulas & constants etc.... and i couldnt get it to run.

 

[mikerickson]

Its not a sub to be run. Its a UDF to be used like an Excel function combined with Conditional Formatting.
Put this Conditional Formatting formula on A1 =HasMixedTerms(A1) and copy that formatting to the cells that you want colored.

Or, for testing the function, you could enter that formula in a different cell.

This version has better error handling.

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)
    If strFormula <> vbNullString Then
        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 If
End Function

 

[Rasec]

Sir,

Would it be possible to send you an excel file with what I'm looking for?

Best,

Rasec

Its not a sub to be run. Its a UDF to be used like an Excel function combined with Conditional Formatting.
Put this Conditional Formatting formula on A1 =HasMixedTerms(A1) and copy that formatting to the cells that you want colored.

Or, for testing the function, you could enter that formula in a different cell.

This version has better error handling.

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)
    If strFormula <> vbNullString Then
        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 If
End Function