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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

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]
 

Forum statistics

Threads
1,141,741
Messages
5,708,238
Members
421,553
Latest member
aichabaddi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top