Conditional Formating based whether the cell is hardcoded, referencing another cell, or performing an operation

PatrickSr

New Member
Joined
Jul 21, 2017
Messages
2
Hey all,

The three text strings below represent 3 different cells:



123
=M19
=SUM(M18:M19)




<tbody>
</tbody>
I would like an easy way to format each of them; not based on their output but based on how that output is derived (i.e. hardcoded, linked to/referencing another cell, calculated using operators).
The =ISFormula can’t distinguish between =M19 and =Sum(M18:M19). Any idea?

Patrick
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,985
stack overflow says

In MS Office 365 Version: 5.0.4667.1002, the following works

  1. Select a range of cells.
    • Case1: Use <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; font-size: 13px; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Ctrl + A</code> to select all cells.
    • Case2: Select a specific range.
  2. Go to the Home tab, Styles section, and choose Conditional Formatting > New Rule.
  3. The "New Formatting Rule" dialog will open.
  4. Choose "Use a formula to determine which cells to format"
  5. In the textbox, add the following rule: <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; font-size: 13px; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">=IsFormula(A1)</code>
    • Case1: If you selected all cells, use <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; font-size: 13px; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">A1</code> because it is the first cell.
    • Case2: If you selected a specific range, replace <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; font-size: 13px; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">A1</code> with the first cell in your range.
  6. Click Format...
  7. The "Format Cells" dialog will open.
  8. Choose the format you would like to apply. E.g. a yellow background.
  9. Click OK.
  10. All cells that have formulas will now have, for instance, a yellow background.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
oldbrewer,
The =ISFormula can’t distinguish between =M19 and =Sum(M18:M19). Any idea?
Based on that, I don't think the question is how to Conditional Format formulas. Rather it is how to differentiate types of formulas with Conditional Formatting.
That is, how to make formulas with just a single cell reference like:
=M19
one color, and then make formulas with functions, like
=SUM(M18:M19)
a totally different color.

The issue is that they are both considered formulas in Excel, so IsFormula behaves the same way on both.

Patrick,
This is an intriguing question. Most of the time Conditional Formatting is used on the values being returned/listed, because doesn't always know what is going to show up there. However, since formulas are usually entered/controlled by the user (where you control that), you usually don't see this type of request from Conditional Formatting. Do you mind if I ask what is the bigger picture here? If we get a better understanding of what your ultimate goal is and why you are trying to do this, we may have alternative solutions for you.

Right now, the only thing I can think of is to use VBA code to loop through all the cells and format them accordingly, though that is not dynamic.
 

PatrickSr

New Member
Joined
Jul 21, 2017
Messages
2
stack overflow says

In MS Office 365 Version: 5.0.4667.1002, the following works

  1. Select a range of cells.
    • Case1: Use <code style='margin: 0px; padding: 1px 5px; border: 0px currentColor; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; font-style: inherit; font-variant: inherit; font-weight: inherit; vertical-align: baseline; white-space: pre-wrap; font-stretch: inherit; background-color: rgb(239, 240, 241);'>Ctrl + A</code> to select all cells.
    • Case2: Select a specific range.
  2. Go to the Home tab, Styles section, and choose Conditional Formatting > New Rule.
  3. The "New Formatting Rule" dialog will open.
  4. Choose "Use a formula to determine which cells to format"
  5. In the textbox, add the following rule: <code style='margin: 0px; padding: 1px 5px; border: 0px currentColor; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; font-style: inherit; font-variant: inherit; font-weight: inherit; vertical-align: baseline; white-space: pre-wrap; font-stretch: inherit; background-color: rgb(239, 240, 241);'>=IsFormula(A1)</code>
    • Case1: If you selected all cells, use <code style='margin: 0px; padding: 1px 5px; border: 0px currentColor; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; font-style: inherit; font-variant: inherit; font-weight: inherit; vertical-align: baseline; white-space: pre-wrap; font-stretch: inherit; background-color: rgb(239, 240, 241);'>A1</code> because it is the first cell.
    • Case2: If you selected a specific range, replace <code style='margin: 0px; padding: 1px 5px; border: 0px currentColor; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; font-style: inherit; font-variant: inherit; font-weight: inherit; vertical-align: baseline; white-space: pre-wrap; font-stretch: inherit; background-color: rgb(239, 240, 241);'>A1</code> with the first cell in your range.
  6. Click Format...
  7. The "Format Cells" dialog will open.
  8. Choose the format you would like to apply. E.g. a yellow background.
  9. Click OK.
  10. All cells that have formulas will now have, for instance, a yellow background.

Indeed, Thank you. However, =IsFormula cannot distinguish between =m18 and =sum(m18:m19). I need to be able to distinguish cells that just reference other cells from ones that perform operations (i.e. sum, product, etc.).

Patrick
 

UniMord

Active Member
Joined
May 6, 2002
Messages
309
This might not be exactly what you're looking for, and you'll need a helper column, because you can't use the union operator in Conditional Formatting.
You can try entering the following 3 conditions, in the following order:

Test that it's not a formula:
Condition #1: =ISNA(FORMULATEXT(A1))

Test for reference:
Condition #2: =B1
In B:B: =ISREF(INDIRECT(REPLACE(FORMULATEXT(A1),1,1,"")) INDIRECT(REPLACE(FORMULATEXT(A1),1,1,"")))

Test for formula:
Condition #3: =ISFORMULA(A1)
 
Last edited:

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,591
Office Version
365, 2016
Platform
Windows
You can use the UDF in conditional formatting
your CF formula would be
Code:
=isformu(A1)
This will text if the cell starts with and = has both ( and ), and that the ( comes before the ).
As long as you do not have a text cell that starts with an = and has a ( before a ) then this should work



Code:
Function isformu(rng As Range)
On Error Resume Next
c = rng.Formula
f = Application.WorksheetFunction.Search("(", c)
l = Application.WorksheetFunction.Search(")", c)
If l = "" Or f = "" Then
    isformu = False
    Exit Function
End If
If IsNumeric(Application.Search("(", c)) And Left(c, 1) = "=" And IsNumeric(Application.Search(")", c)) And f < l Then
    isformu = True
Else
    isformu = False
End If
End Function
 
Last edited:

UniMord

Active Member
Joined
May 6, 2002
Messages
309
Scott, For that, you could have used =SEARCH("=*(*)*", A1), without a UDF, but it has a few flaws.
What about a formula with just operators, for example: =5 * 50 / 3
What about a reference that has parentheses in it, for example: =(A9) or = Sheet1 (Today)!A9
 

UniMord

Active Member
Joined
May 6, 2002
Messages
309
This might not be exactly what you're looking for, and you'll need a helper column, because you can't use the union operator in Conditional Formatting.
You can try entering the following 3 conditions, in the following order:

Test that it's not a formula:
Condition #1: =ISNA(FORMULATEXT(A1))

Test for reference:
Condition #2: =B1
In B:B: =ISREF(INDIRECT(REPLACE(FORMULATEXT(A1),1,1,"")) INDIRECT(REPLACE(FORMULATEXT(A1),1,1,"")))

Test for formula:
Condition #3: =ISFORMULA(A1)
Correction: I meant intersection operator, not union operator.
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,985
1001X99926chchar>>>"=C1+D1
1003Xyes=C1+D1*2
1001X=SUM(C1:D1)
1998X=C1*D1
abcdefabcdef
fred +X="fred"&" +"
this macro has made the first 4 and the last cell in col A yellow
and has put an "X" against them
the delay in the code was for test purposesthe lookup table from sheet 2
as are the use of cells F1 and F2
you can delete these if you like-yes
*yes
obviously A1 is a formula and shows the answer,no
,no
the formulas as typed in are in column N/yes
^yes
+yes
note the macro converts each cell in col A to text=yes
and then checks each character against>no
a lookup table
this is located on sheet 2 but a copy of it
is shown to the right for clarity
Sub Macro4()
'
' Macro4 Macro
' Macro recorded 24/07/2017 by bob
'
'
Cells.Replace What:="=", Replacement:="'", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False
j = 1: k = 1
4 Cells(1, 11) = Mid(Cells(j, 1), k, 1)
5 Cells(1, 6) = j
Sheet2.Select
thisanswer = Cells(2, 9)
Sheet1.Select
Cells(2, 6) = thisanswer
For delay = 1 To 9999999: Next delay
If thisanswer = "yes" Then GoTo 30 Else GoTo 20
20 k = k + 1
If k < 51 Then GoTo 4 Else GoTo 40
30 Cells(j, 1).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Cells(j, 2) = "X"
If thisanswer = "yes" Then GoTo 35 Else GoTo 40
35 Cells(j, 1) = "=" & Cells(j, 1)
40 j = j + 1: k = 1: If j = 7 Then GoTo 100
GoTo 4
100 End Sub

<colgroup><col><col span="8"><col><col span="3"><col><col span="2"></colgroup><tbody>
</tbody>
 

UniMord

Active Member
Joined
May 6, 2002
Messages
309
I just came up with a MUCH better answer than the one I originally gave; it doesn't require a helper column at all. For the sake of completeness, here is the entire solution:

Test that it's not a formula:
Condition #1: =NOT(ISFORMULA(A1))

Test for reference:
Condition #2: =INDIRECT(REPLACE(FORMULATEXT(A1),1,1,""))

Test for formula:
Condition #3: =ISFORMULA(A1)

Note: The test for reference must be before the test for formula, or it'll get trapped by the formula test.
 
Last edited:

Forum statistics

Threads
1,082,151
Messages
5,363,430
Members
400,736
Latest member
Aida

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top