Conditional formatting to identify hard-coded cells

[simonalcock]

Hi all,

I have a spreadsheet in which some cells have lost their formulas. The calculated value is still displayed, but it no longer updates with the rest of the sheet (as if F2, F9 had been pressed).

I need to find out which cells contain hard-coded numbers rather than formulas, and have been trying to use conditional formatting to highlight them for me.

I have a solution, but it seems to make the workbook terribly slow. If anyone has a simpler solution to this problem, I'd love to hear your ideas. My solution follows:

Define the following function in VBA:

Function IsFormula(cell As Range)
    IsFormula = cell.HasFormula()
End Function

Apply conditional formatting to the required range of cells, using the following formula to determine which cells to format:

=AND(NOT(OR(isformula(A1),ISBLANK(A1))),ISNUMBER(A1))

 

[GlennUK]

How many cells are you conditionally formatting this way?

 

[Stormseed]

Manually, if you need to know if a cell contains a formula or not, Select the range and press Ctrl+` - A combination of CONTROL and TILDE. It will show all and every formula the worksheet has at the moment.

I hope it helps you.

 

[Peter_SSs]

simonalcock

Welcome to the MrExcel board!

I'm wondering if you really need a macro and/or conditional formatting to achieve your goal. Would this be any use?
1. Select all cells by clicking the grey rectangle just above the row 1 label.
2. Edit|Go To...|Special...|Constants|OK
3. Apply a colour directly if you wish

 

[Jon von der Heyden]

Not sure why the UDF doesn't work quickly enough...

But if you really want to use conditional format consider using XLM4.

Instructions:
1. Create a new name - Insert > Name > Define
2. Call it Has_formula
3. In Refers to: input the following formula:
=GET.CELL(48,INDIRECT(ADDRESS(ROW(),COLUMN(),4)))
4. Add > Ok

5. Go to cell you want to apply conditional format
6. Format > Conditional Formatting
7. Choose Formula is
8. Input the following formula:
=NOT(Has_Formula)
9. Click Format
10. Choose desired format
11. Ok > Ok

Hope this does the trick for you.

Regards
Jon

 

[simonalcock]

You guys are awesome - the Go To Special command is just what I needed.

I used Go To Special, and chose Constants, numbers only.



Glenn - the workbook was noticably slow with conditional formatting applied only to a small test range.

Stormseed - I tried your method, but I find it easier to spot the odd cells if they are colour-coded.

 

[Stormseed]

Instructions:
1. Create a new name - Insert > Name > Define
2. Call it Has_formula
3. In Refers to: input the following formula:
=GET.CELL(48,INDIRECT(ADDRESS(ROW(),COLUMN(),4)))
4. Add > Ok

5. Go to cell you want to apply conditional format
6. Format > Conditional Formatting
7. Choose Formula is
8. Input the following formula:
=NOT(Has_Formula)
9. Click Format
10. Choose desired format
11. Ok > Ok

WOW

 

[jamilm]

Jon,

thanks for this. i found it very useful. however i have a little question and would appreciate if you could shed light

i understand that after Get.cell you used 48 which is If the cells contains a formula, returns TRUE; if a constant, returns FALSE. also the rows and columns and my main question is you used "4" at the end what is the role of this digit 4 at the end of formula?

Not sure why the UDF doesn't work quickly enough...

But if you really want to use conditional format consider using XLM4.

Instructions:
1. Create a new name - Insert > Name > Define
2. Call it Has_formula
3. In Refers to: input the following formula:
=GET.CELL(48,INDIRECT(ADDRESS(ROW(),COLUMN(),4)))
4. Add > Ok

5. Go to cell you want to apply conditional format
6. Format > Conditional Formatting
7. Choose Formula is
8. Input the following formula:
=NOT(Has_Formula)
9. Click Format
10. Choose desired format
11. Ok > Ok

Hope this does the trick for you.

Regards
Jon

 

[muggi]

Not sure why the UDF doesn't work quickly enough...

But if you really want to use conditional format consider using XLM4.

Instructions:
1. Create a new name - Insert > Name > Define
2. Call it Has_formula
3. In Refers to: input the following formula:
=GET.CELL(48,INDIRECT(ADDRESS(ROW(),COLUMN(),4)))
4. Add > Ok

5. Go to cell you want to apply conditional format
6. Format > Conditional Formatting
7. Choose Formula is
8. Input the following formula:
=NOT(Has_Formula)
9. Click Format
10. Choose desired format
11. Ok > Ok

Hope this does the trick for you.

Regards
Jon

Sorry for keeping this old thread alive, but above is very usefull.

Is it somehow possible to insert this conditional formatting in cell to the right of the cell being checked, if it containg a formual or not.

Column G either contains a formula or a value (and needs to be checked is its true or false)
i want Column H to contain above Quoted Formal/conditional formatting, so example: H4 cell get a fill color each time G4 do not contain a formular.

I can not create the formula for this ?

 

[muggi]

maybe with some sort of IF formula ?

Have no option for editing my above post!?