Easy way to highlight all cells that contain Formulas

[gmooney]

I have a sheet with 100's of rows and columns, many of which contain formulas and many that just contain data.

I want to do conditional formatting to show any cell that is a formula vs data.

I know I can do the "show formulas" option but that doesn't work for me.

Can I create some sort of Conditional Formula to achieve this?

 

[jolivanes]

Sub Maybe()
    ActiveSheet.SpecialCells(xlCellTypeFormulas).Select
End Sub

 

[gmooney]

Sub Maybe()
    ActiveSheet.SpecialCells(xlCellTypeFormulas).Select
End Sub

Hi this gives me an error message Run Time 438. Object doesn't support this property or method.

 

[Michael M]

It's missing a .cells reference

Sub Maybe()
    ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas).Select
End Sub

 

[gmooney]

It's missing a .cells reference

Sub Maybe()
    ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas).Select
End Sub

Hi,

I just found out this works.

=ISFORMULA(A2) for conditional formatting formula and it works beautifully.

Thanks you!

 

[jolivanes]

Thanks Michael.
You've got it through CF so all is well.
Good Luck

 

[Peter_SSs]

I just found out this works.

=ISFORMULA(A2) for conditional formatting formula and it works beautifully.

Possibly is exactly what you want but with MS 365 and dynamic array formulas just check this circumstance is still what you want.

In the mini sheet below, it is true that cell A2 is the only cell that actually contains an entered formula but cells A3:A5 are not hard-coded values and do flow from the formula in A2.
If I select say A4, the formula bar does show a formula, though it is pale grey and cannot be edited in that cell.

23 07 11.xlsm
	A	B
1
2	1	1
3	2	2
4	3	3
5	4	4
6
CF Formulas

Cell Formulas
Range	Formula
A2:A5	A2	=SEQUENCE(4)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B6	Expression	=ISFORMULA(A2)	text	NO