Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello all,

    I just tried to highlight cells, containing formulae in a worksheet to see the difference to inputcells (containing values).
    I got out a mixture with conditional formatting and vba ->

    Function IsFormula(MyCell As Range)
    Application.Volatile
    IsFormula = MyCell.HasFormula
    End Function

    and then condī. Format =isformula(C15)=TRUE

    how can i manage to get this in vba only to set the condition, if has formula then highlight in interior.colorindex = XX.???

    maybe You got a suggestion for me, please?


    stefan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Instead of conditional formatting you could use the Worksheet_Change event, but that would disable the undo feature, so I don't think it's an attractive alternative.
    Or you could run a macro whenever you want to review the sheet. You shouldn't do this if you have used fill colors for cells, because they will be overwritten by the macro.
    Code:
    Sub HighlightFormulas()
      Dim oCell As Range
      ActiveSheet.Cells.Interior.ColorIndex = xlColorIndexNone
      For Each oCell In ActiveSheet.UsedRange
    	If oCell.HasFormula Then
    	  oCell.Interior.ColorIndex = 6
    	End If
      Next oCell
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thats pretty cool,

    thank You.

    ps: For this case I only have to get out the difference between formulae and values so it does not matter to lose the
    undo function, but i know what You mean, thanks again.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could also select Edit | Go to... (or press Ctrl+G), click Special..., select Formulas and click OK. This will select all cells that have a formula.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='762826' date='02-Mar-2009 16:36']You could also select Edit | Go to... (or press Ctrl+G), click Special..., select Formulas and click OK. This will select all cells that have a formula.[/quote]


    You are absolutely right, i know this method, but as a "performing" lazybone....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •