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
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)
RE: Highlighting data (as opposed to formulas)
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)
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)
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 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)
Ctrl - ` (the ` is the unused key to the left of the 1)
RE: Highlighting data (as opposed to formulas)
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)
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)
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