Identifying Formula or Number in a cell

[kingspur06]

Hi everyone. I have a spreadsheet which calculates monthly instalments via a formula that links to a total and start/end dates in the sheet. However there are occasions when I need to manually enter the values because they need to be different values in different months. Of course this means that the total and dates fields become irrelevant for that particular record.
This creates a risk that a user will change the total or dates and not realise the monthly instalments have not updated because there is no longer a formula in the instalment cells.
So I would like to create a flag which will identify when a cell only contains a number and not a formula.
I have not been able to find anything that suggests this is possible as Excel recognises the cell as a number whether it has been created via a formula or not.
So thought I would see if anybody knows of a way to make Excel differentiate between a formula and a manually entered number.
many thanks for any suggestions you have
Mark

 

[bebo021999]

Like this?
when a sheet is activated, all cells in specific range, i.e, A1:F100, with constant values will be added with "comment"
(Right click on sheet name/ view code)

Option Explicit
Private Sub Worksheet_Activate()
Dim cell As Range
For Each cell In ActiveSheet.Range("A1:F100").SpecialCells(xlCellTypeConstants)
    cell.AddComment "constant values"
Next
End Sub

 

[kingspur06]

Like this?
when a sheet is activated, all cells in specific range, i.e, A1:F100, with constant values will be added with "comment"
(Right click on sheet name/ view code)

Option Explicit
Private Sub Worksheet_Activate()
Dim cell As Range
For Each cell In ActiveSheet.Range("A1:F100").SpecialCells(xlCellTypeConstants)
    cell.AddComment "constant values"
Next
End Sub

Many thanks - that works really nicely. Is it possible instead of adding a comment to have the cell change colour instead, rather like a conditional formatting rule?
I don't speak VBA I'm afraid

 

[kingspur06]

Many thanks - that works really nicely. Is it possible instead of adding a comment to have the cell change colour instead, rather like a conditional formatting rule?
I don't speak VBA I'm afraid

Ignore that - I was being lazy but a quick google showed me how to change the colour

Option Explicit
Private Sub Worksheet_Activate()
Dim cell As Range
For Each cell In ActiveSheet.Range("A1:F100").SpecialCells(xlCellTypeConstants)
cell.Interior.ColorIndex = 5
Next
End Sub

many thanks once again - what a great forum this continues to be

 

[kingspur06]

sorry me again....this code works great when there is a manually entered number in the cell range, but it seems to throw up a runtime error if the range only contains formulas.
Is there a tweak to the code that would stop this from happening?
many thanks

 

[bebo021999]

Try with error trap:

Option Explicit
Private Sub Worksheet_Activate()
Dim cell As Range
On error resume next
For Each cell In ActiveSheet.Range("A1:F100").SpecialCells(xlCellTypeConstants)
cell.Interior.ColorIndex = 5
Next
End Sub

 

[kingspur06]

Option Explicit Private Sub Worksheet_Activate() Dim cell As Range On error resume next For Each cell In ActiveSheet.Range("A1:F100").SpecialCells(xlCellTypeConstants) cell.Interior.ColorIndex = 5 Next End Sub

Perfect - that works. many thanks again

 

[mikerickson]

You can do this with Conditional Formatting with the formula =(CODE(FORMULATEXT(A1))=CODE("="))

 

[kingspur06]

Perfect - that works. many thanks again

So something that I had not factored in, is that I need to use this on an Excel Shared sheet. I did not realise my macro would not transfer over when I shared my local sheet. Would you happen to know the translation that I need to convert this vba code into a Excel Shared sheet script?

Option Explicit
Private Sub Worksheet_Activate()

   ActiveSheet.Range("o2:aa2000").SpecialCells(xlCellTypeConstants).Interior.ColorIndex = 17

End Sub