Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Mar 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Differing formats for fomulas and constants

    Here is what I want to do. I have a large spreadsheet with forecast numbers. All cells, a 20 by 100 matrix, are formulas. However, there are cases when the calculated forecast is way off and I will have to manually input a number. Problem is that next time I will open the file, I will not know which forecasts have been updated (formula) and which ones are the hard numbers I inputed last time I worked with the file. Is there a way to conditionally format the cells to visually tell me which cells have formulas and which have hard numbers (maybe color the cell red if it has a constant in it). Any help would be greatly appreciated. Thanks in advance,

    altin kalo

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Differing formats for fomulas and constants

    Can't figure out how to do it with conditional formatting. An alternative would be to highlight all the cells, then Edit, Go To... (or Ctrl+G). Press the Special... button at the bottom of the resulting dialog box. From there you can select the Constants option button, then press the OK. Result is that all constants will be selected. You could then format them how you like.
    If I figure out the conditional formatting, I'll post again.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Differing formats for fomulas and constants

    Hi,
    I don't know of any way to do it without code - I couldn't think of a way to do it with Conditional Formatting.
    However this code should do it:
    Sub HighlightFormulae()
    Dim myCell As Range
    For Each myCell In ActiveSheet.UsedRange
    With myCell
    If .Formula <> "" Then
    If Not .HasFormula Then
    .Font.Color = vbRed
    Else
    .Font.Color = vbBlack
    End If
    End If
    End With
    Next
    End Sub
    This will check any cell that has something in it, and if it's not a formula, change the font to red.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Differing formats for fomulas and constants

    I have two suggestions for you.

    1- On the sheet, select "Go To" from the Edit menu. On the bottom of the dialog box press the "Special" button. In the resulting dialog box, click on the check box next to "Constants" and then click on Ok. This should select all cells on the sheet that have a constant value in them and you can see where the formula has been replaced.

    2- Go to a new sheet in the workbook and put a formula in A1 that refers to cell A1 on your original sheet. If the original sheet is named Sheet1, then the formula in the new sheet would be =Sheet1!A1. Copy this formula to all cells on the new sheet where there are formulas on the original sheet. Replace the formulas on this sheet with the constants. When you are done, just delete this sheet and create another one (should take about two minutes). If you have special formatting on the original sheet, just copy the original sheet and do a Paste Special Formats to the new sheet. This method will cause problems if you have merged cells on the original, in this case the paste will fail (that's and Excel bug).
    Legare Coleman

  5. #5
    Lounger
    Join Date
    Mar 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Differing formats for fomulas and constants

    Thanks Rory. Works like a charm.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Differing formats for fomulas and constants

    Rory:
    I am trying to teach myself VBA. Can you anser a question re this code. Why do you need the statement: "If .Formula <> "" Then" ?
    Stephen

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Differing formats for fomulas and constants

    Hi Stephen,
    That line is there so that it only checks cells that have data of some sort in them and then determines what colour they should be. Otherwise you're changing the font colour of all the blank cells too.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Differing formats for fomulas and constants

    Just because it bugged me, I did figure out one way to use conditional formatting. If you create a custom function as follows:

    Private Function IsConstant(rng As Excel.Range) As Boolean
    If rng.Formula <> "" And Left(rng.Formula, 1) <> "=" Then
    IsConstant = True
    Else
    IsConstant = False
    End If
    End Function

    ... then you can use something like =IsConstant(A1)=TRUE in the Conditional Formatting dialog box (with Condition 1 set to "Formula Is").

    Since it still involves writing VBA code, though, I wouldn't think it's worth converting from your solution.

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Differing formats for fomulas and constants

    Hi Colin,
    That's a great tip though! I'd never even thought of using custom functions with conditional formatting - opens up a whole new world of possibilities.....
    Thanks!
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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