Conditional Formatting-cells with hard typed values

[Suzannah Robertson]

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

 

[pedie]

I do not know of any formula but using UDF yes we can.

enter this in module

[/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]

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]

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

 

[Suzannah Robertson]

Thanks Haseeb

 

[Ajay23sms]

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]

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]