Conditional Formatting-cells with hard typed values

Suzannah Robertson

New Member
Joined
Jan 13, 2010
Messages
23
Hello,
I need to be able to highlight cells that contain hard typed numbers instead of formulas.
I'm using Excel 2007 and need to be able to spot in a worksheet which cells have had the formulas overwritten.
Can anyone help?
thanks very much,
S
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
I do not know of any formula but using UDF yes we can.

enter this in module
Code:
[/FONT]
[FONT=Courier New]Option Explicit
Function ISFORMULA(cel As Range) As Boolean
    ISFORMULA = cel.HasFormula
End Function[/FONT]
[FONT=Courier New]

then select the cell and enter conditional formating
=ISFORMULA(A1)

If cell has formula it will show your formatting else not.

Hope it helps.
 

Suzannah Robertson

New Member
Joined
Jan 13, 2010
Messages
23
thanks pedie,

I'm not getting it to work.. probably something I'm doing wrong.
I've entered your formula in the module, and now it's being recognised as a function.
in the conditional formatting I'm entering

=ISFORMULA(A1)

in the 'use formula to determine which cells to format Rule Type.
is this correct?

thanks,
S
 

Haseeb Avarakkan

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
Hello, Try

Select A1, then define a name Ctrl+F3

Name: HasFormula

refers to: =GET.CELL(48,A1)

Then select the range you want to apply the conditional format enter this formula

=AND(NOT(HasFormula),ISNUMBER(A1))

Choose the format.

Change A1 to your first cell in the CF range
 

Ajay23sms

New Member
Joined
May 12, 2011
Messages
2
Vlookup Formula in VBscript

Hi all,

I got list of all account no in Sheet3 i need to do Vlookup formula in VB referring to Sheet 2.
Sheet 3
3122131
2314534
2312132

Sheet 2.
Account GCCOA
225145 21130
125455 21545
121231 1212
 

Ajay23sms

New Member
Joined
May 12, 2011
Messages
2
Re: Vlookup Formula in VBscript

Hi all,

I got list of all account no in Sheet1i need to do Vlookup formula in VB referring to Sheet 2. i need vlookup in Sheet 3.
Sheet 1
3122131
2314534
2312132

Sheet 2.
Account GCCOA
225145 21130
125455 21545
121231 1212[/QUOTE]
 

Watch MrExcel Video

Forum statistics

Threads
1,109,541
Messages
5,529,436
Members
409,877
Latest member
DDhol
Top