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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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>
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top