×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Highlighting data (as opposed to formulas)

Highlighting data (as opposed to formulas)

Highlighting data (as opposed to formulas)

(OP)
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

RE: Highlighting data (as opposed to formulas)

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.

RE: Highlighting data (as opposed to formulas)

TG

I have done something similar with conditional formating.

set up conditional formatting like this...

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

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

Hope this helps...
Have a great day!

Scott

RE: Highlighting data (as opposed to formulas)

(OP)
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

RE: Highlighting data (as opposed to formulas)

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)  ("")

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

RE: Highlighting data (as opposed to formulas)

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

RE: Highlighting data (as opposed to formulas)

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)

RE: Highlighting data (as opposed to formulas)

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

RE: Highlighting data (as opposed to formulas)

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

RE: Highlighting data (as opposed to formulas)

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources