marking cells that do not have a formula?

[Todd]

I have a worksheet that the formula's are overriden
freeequently (thats the way its supposed to be). Is there
a way to mark the cells that now contain numbers and no
formula? I have a macro that colors cells that contain
formulas. I need the opposite too. Can I modify it?

Sub markformula()
Cells.SpecialCells(xlCellTypeFormulas).Interior.ColorIndex
= 35
End Sub

TIA

Todd

 

[Ron de Bruin]

Hi Todd

Sub test1()
Cells.SpecialCells(xlCellTypeConstants, xlTextValues).Interior.ColorIndex = 3
End Sub

Sub test2()
Cells.SpecialCells(xlCellTypeConstants, xlNumbers).Interior.ColorIndex = 5
End Sub

Try this

The first color the text and the second the numbers

 

[Gord Dibben]

Todd

Sub SELECTTEXT()
With Selection
Selection.SpecialCells(xlCellTypeConstants, 2).Select
End With
End Sub

Sub SELECTNUMS()
With Selection
Selection.SpecialCells(xlCellTypeConstants, 1).Select
End With
End Sub

Sub SELECT_FORMULAS()
If Not Selection.HasFormula Then
MsgBox "Cells do not contain formulas"
End
End If
With Selection
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
End With
End Sub


Gord Dibben Excel MVP - XL97 SR2 & XL2002