Highlighting data (as opposed to formulas)

[trainguy]

Hi all.

I need to find a way to have all cells containing numbers that need to be input show up, for example, red. This is very handy for foolproofing the sheet, and troubleshooting. Right now, I manually format the "number" cells to show red, bold numbers, and I keep everything else (formulas, cell references etc.) the default black.

I've tried conditional formatting to no avail.

Thanks in advance for any help.

tg

 

[yakpol]

Try to use Format-styles. You format cell just once and then reuse this formating for all input cells just with one click. The useful technic here is to select a bunch of cells at once by pressing Ctrl key and left click button in the same time.

 

[ByDesign]

TG

I have done something similar with conditional formating.

set up conditional formatting like this...

if (cell value is) (less than or equal to) ("&quot

( ) = the input box
excel will change the "" into =""""

Hope this helps...
Have a great day!

Scott

 

[trainguy]

Thanks Scott,

That's almost what I need. This unfortunately does not distinguish between numbers typed in and formulas. I guess I'll bite the bullet and continue to do it manually.

Cheers,
tg

 

[ByDesign]

Trainguy

I did a little tinkering... The results I had from the previous example was:
1) Numeric data did not clear the formatting
2) alpha charcters did.


I think I found a solution for you with a small change.

set up conditional formatting like this...

if (cell value is) (equal to) ("&quot

Hope this helps.

( ) = the input box
excel will change the "" into =""

The results for this are:
1) any alpha or numberic character will turn off the conditional formatting
2) you can't have a formula in the cell that has conitional formatting like this becasue the result of the formula results in the condtional formatting evaluating to something other than ""






Have a great day!

Scott

 

[macajm]

You may be able to adapt the following macro which I use to do the opposite (ie to shade the cells containing a formula)
You need to select the cells first that you want to check or else click in the top left corner to select all cells of the spreadsheet, then run the macro to set the format.
[i acknowledge that someone on this forum gave me the clue ages ago, but cannot remember who]

Sub Conditionally_Formatting_Formula_Cells()
' Conditionally_Formatting_Formula_Cells Macro
' Macro recorded 8/02/2002 by AMcKim
' This macro shades any (preselected)cells on a spreadsheet
' which contain a formula
'
ActiveWorkbook.Names.Add Name:="IsFormula", RefersToR1C1:= _
"=LEFT(GET.CELL(6,INDIRECT(""rc"",FALSE)),1)=""="""
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=IsFormula"
Selection.FormatConditions(1).Interior.ColorIndex = 15
End Sub

 

[Tim401]

how about the Shortcut key that toggles between view normal and view formulas

Ctrl - ` (the ` is the unused key to the left of the 1)

 

[Tim401]

Alternaticelly heres a quick and dirty macro that selects all cells with formulae and formats them White-on-black

Sub HighlightFormulae()

Selection.SpecialCells(xlCellTypeFormulas, 23).Select
With Selection.Font
.ColorIndex = 2
End With
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With

End Sub

 

[Wickus]

The following method can be used after the fact to format the inputs.

Use GOTO (either F5 or Cntrl G or Edit, Go To), click on SPECIAL..., then select Constants and clear the boxes with Text, Logical and Errors, thus leaving only Numbers. Click OK. All the cells with numbers (presumably all your inputs) are now selected. Apply the desired formatting to all cells.

Regards.

Wickus

 

[trainguy]

This is really weird, Wickus.

I just signed onto this board today to share with all of the above my discovery of the GOTO feature, exactly as you describe it.

That's precisely what I needed.

Thanks, everyone. I'm going to buy a lottery ticket tonite, and possibly get out of this number-crunching, proposal preparing, mind-numbing, infinite-element analyzing, spec. writing (re-wording), engineering drawing reviewing (groooooan) nightmare I call a career.

Better close up the venting valve before I drive home.
Thanks for listening, and responding, all of you.
(all of a sudden I feel like Johnny Carson about to retire...)

tg