How to easily distinguish between formula cells and value cells?

[CaraM]

I'm building a large and complicated workbook for retirement investment strategizing.

I'm forecasting with formulas, on a yearly basis but, when a year has closed, i would like to enter my actual values throughout the workbook.

I'd really like to HIGHLIGHT those cells that contain "manual entries" (i.e., values) to distinguish them from those that still contain "future estimates" (i.e., formulas).

This would be WORKBOOK-WIDE, and I've got concerns about doing that with conditional formatting, but I'll use it if it's the only way.

Is there any conditional formatting - or other method - for distinguishing between cells containing values and those containing formulas?

Thanks

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Depending on your xl version it would either have to be a macro, or conditional formatting.

 

[CaraM]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Depending on your xl version it would either have to be a macro, or conditional formatting.

Thanks - I've updated my profile. I use MS 365 on Windows 10.

I'd be interested in suggestions for either VBA or conditional. I was hoping there'd be something like ...

Wait - I just found something I can use. It's not automatic, but it also doesn't add to my file size like VBA and conditional formatting have done, in the past. I can use Home > Find & Select > Constants, unselect my column headers, and the highlight the cells manually. Not perfect, but does what I need mostly, I think!

 

[Peter_SSs]

You can have dynamic results with Conditional Formatting

21 12 15.xlsm
	A	B	C
1	2	12
2
3	abc	def	abcdef
CF Constants

Cell Formulas
Range	Formula
B1	B1	=6*A1
B2	B2	=IF(C2="","",C2)
C3	C3	=A3&B3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:C3	Expression	=AND(LEN(A1)>0,NOT(ISFORMULA(A1)))	text	NO